Needing Clarity on SQL Join Query -

having trouble understanding query, particularly in subquery. don't accomplishing. appreciated. thanks

# find largest country (by area) in each continent. show continent, # name, , area. select continent, name, area countries area = (  select max(area)  countries b  a.continent = b.continent ) 

consider following subset of countries data:

 continent      country    area north america   usa     3718691 north america   canada  3855081 north america   mexico  761602 europe          france  211208 europe          germany 137846 europe          uk      94525 europe          italy   116305 

this correlated query behaves follows:

1. reads first row returned outer query (north america, usa, 3718691)
2. runs subquery correlates a.continent, north america, , returns 3855081 maximum area in north america.
3. does equality checks see if 3855081 matches area on row we're working on.
4. it doesn't match next row in outer query read , start on @ step 1 time working on second row.

5. repeat rows in outer query.

   when we're looking @ rows 2 , 4, step 4. match rows returned query.

   you can check results using data in countries table , running query.

   note poor way determine country maximum area per continent because repeats subquery every country. using sample data, determines maximum area north america 3 times , maximum area europe 4 times.

since asked in comment, write query follows:

select a.continent, a.name, a.area countries      inner join (select continent, max(area) max_area                  countries                  group continent) b on a.continent = b.continent a.area = b.max_area 

in version of query, maximum each continent determined once. original query written illustrate correlated queries , it's important understand them. correlated queries can used resolve complex logic.