sql - Is it a slow query? Can it be improved? -

sql - Is it a slow query? Can it be improved? -

i going through sqlzoo "select within select tutorial" , here's 1 of queries did job (task 7)

world(name, continent, area, population, gdp)

select w1.name, w1.continent, w1.population world w1 25000000 >= all(select w2.population world w2 w2.continent=w1.continent)

my questions effectiveness of such query. sub-query run each row (country) of main query , repeatedly re-populating list given continent.

should concerned or oracle optimization somehow take care of it? can reprogrammed without correlated sub-query?

if want rewrite query without correalted subquery, here 1 way:

select w1.name, w1.continent, w1.population world w1 bring together ( select continent, max(population) max_population world grouping continent ) c on c.continent = w1.continent 25000000 >= c.max_population ;

i not imply faster. oracle's optimizer pretty , simple overall query, write it. here's simplification:

select w1.name, w1.continent, w1.population world w1 bring together ( select continent world grouping continent having max(population) <= 25000000 ) c on c.continent = w1.continent ;

sql oracle query-optimization subquery correlated-subquery