How does SQL subqueries use outside variables

Question

select sName, GPA, sID
from Student S1
where not exists (select sName from Student S2 where S2.GPA > S1.GPA)

Say the above query. How does the following line work?

(select sName from Student S2 where S2.GPA > S1.GPA)

Is a new table that is the cross product or S1 and S2 created and the GPA compared then returned? I don't understand how S2 can be compared to S1 when S1 is not part of the subquery.

question from:https://stackoverflow.com/questions/65911681/how-does-sql-subqueries-use-outside-variables

Answer 1

This is a correlated subquery.

You can think of it as a nested loop. For each row in the outer table, the subquery is run. And when it is run, the value of S1.GPA is the value from the row in the outer query.

So, the subquery returns one or more rows that have a higher GPA. If there are no such rows, then the GPA in the outer query is the highest one! Voila! One way to get the students with the maximum GPA.

Although you can understand the execution as a nested loop, the SQL optimizer does not need to use a nested loop to run such a query. For instance, if there is an index on Student(GPA) then the query might be quite fast.