SQL co-related sub-query and the case where imagination becomes unimaginable

Question

Greetings and thanks for reading!

I am having a bit of trouble understanding exactly how co-related sub-query work and I hope to rectify this problem by asking some questions here. Please feel free to correct me if I am wrong on any instance.

Here is my understanding of it so far:

1. co-related sub-query is a type of inter-dependent query between outer-select and inner-select

2. outer-select feeds inner-select one of its row data at a time until it reaches the end of its table

3. the inner-select takes one row from the outer-select at a time to match against its entire table, if there is a match, the row is added to the result table

4. At certain point all rows that meets the inner-select condition are added to the resultant table and the inner-select returns a table to the outer-select. At this point, the resultant table is used to check against the condition at the outer-select WHERE clause.

5. finally, the rows that met the condition of the outer-select is return in the resultant set.

Now, my other question is related to this query.

   SELECT continent, name, area FROM world x
  WHERE area >= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
          AND area>0)

Here is the table format.

The above query outputs "the largest country (by area) in each continent with respect to the continent, the name and the area". here is the answer.

The continent field in the database repeats, it could be like this:

continent country
Europe    Italy
Europe    Britain
Africa    Zimbawea
Europe    France

If someone looks at the query in question above, how could that person arrive at this statement "the largest country (by area) in each continent with respect to the continent, the name and the area"? It doesn't look obvious to me. Conversely, given a question such as "Find the largest country (by area) in each continent, show the continent, the name and the area", what will I need to know to construct the query?

Thanks!

Answer 1

The ALL condition compares "a value to every value in a list or returned by a query" (from the documentation). So for this statement ...

SELECT continent, name, area FROM world x
WHERE area >= ALL
    (SELECT area FROM world y
     WHERE y.continent=x.continent
     AND area>0)

... the sub-query returns all the area for the co-related continent, and the ALL condition evaluates to TRUE if the area in the outer query is as large as the largest.

Many people find the ALL (and ANY, SOME) conditions unintuitive. A clearer formulation would be:

SELECT continent, name, area FROM world x
WHERE area = 
    (SELECT max(area) FROM world y
    WHERE y.continent=x.continent)

An alternative approach uses an analytical function:

 SELECT continent, name, area FROM (
     SELECT continent, name, area,
            rank() over (partition by x.continent
                         order by x.area desc ) rnk
     FROM world x 
 )
 where rnk = 1

Anlaytic functions are highly neat. Find out more.