Data sources that are used in the FROM clause were subject to correlation limitations prior to the introduction of the ANSI SQL 99 standard. In this video, learn how to use lateral joins to overcome these limitations and explore a few common use cases for lateral joins that allow for simplification of nested queries.
- [Instructor] One of the limitation of joins … is that each table expression that's being joined … must stand alone and be independent of any other. … So this query is valid. … Each table expression is complete and independent. … But if we introduce a correlation between the two, … we're going to get an error. … F1 Bar is an unknown expression. … And the reason is that it does not exist … in the context of the sub query. … Introducing such correlations could have been very useful … for several types of challenges. … And the most common one is known as … the top end per group challenge. … We need to show all animals … with their most recent vaccination. … Let's start with the animal attributes, names, species, … color, and breed, … and all that's missing is the last vaccine. … I often see developer use correlated expression sub-queries … for these types of challenges. … Select vaccine from vaccinations. … Predicate for the same animal, … and add a limit to the result to get only the latest one. …
This course was created by Ami Levin. We are pleased to offer this training in our library.