Recently I often made some mistakes about subqueries, so I wrote this blog about correlated subqueries.
Firstly, here is a SQL practicing website: https://sqlzoo.net.
It is free and easy for SQL beginners to do SQL exercise step by step.
A correlated subquery works like a nested loop: the subquery only has access to rows related to a single record at a time in the outer query.
The technique relies on table aliases to identify two different usages of the same table, which means one usage is in the outer query and another one is in the subquery.
Here is a table called world, which is an example on the sqlzoo website:
Question: Find the largest country (by area) in each continent, show the continent, the name and the area.
SQL answer using subquery:
SELECT continent, name, area FROM world x WHERE area >= ALL (SELECT area FROM world y WHERE y.continent=x.continent AND area>0)
One way to interpret the SQL line in the WHERE clause that references the two table is “… where the correlated values are the same”.
In this example, we can tell “select the country details from the world table where the area is larger than or equal to the area of all countries where the continent is the same”.
If you are interested in or have any problems with SQL, feel free to contact me.
Or you can connect with me through my LinkedIn.