Max Function in Corelated subquery. To find the country with highest DensityPerKmSq in each continent.

In the below queries, 'a' and 'b' are alias names. The below query is known as corelated Subquery. This is because the inner query is dependent on the outer query. For each continent, the maximum of b.DensityPerKmSq is fetched in the inner query and is equalled to the a.DensityPerKmSq of the outer query.

Here in the second query, 'where b.DensityPerKmSq is not null' is included because null is an unknown value and hence '>=ALL' (select b.DensityPerKmSq from countrytable b where b.continent = a.continent) means '>=ALL' (ALL the Known values of b.DensityPerKmSq for that continent and also the unknown values for that continent).Hence the query will return zero rows for those continents which has atleast one country whose DensityPerKmSq is null, if 'where b.DensityPerKmSq is not null' is not included. In our case, if 'where b.DensityPerKmSq is not null' is not included, then the output will have only two rows(for continents Asia and America).This is because Australia has null value for DensityPerKmSq and Europe has null value for DensityPerKmSq for the country France. Hence the continents, Australia and Europe will not come in the output if 'where b.DensityPerKmSq is not null' is not included.

Further more, '>=ALL' is included because '>=' alone can handle only single value. But the query,
'(select b.DensityPerKmSq from countrytable b where b.DensityPerKmSq is not null and b.continent = a.continent)' returns multiple values.To handle multiple values, '>=ALL' is included.