How subquery works?

My understanding of the logical order of execution of sql query is that

FROM which gives us the base set of data

WHERE which filters the base set data

SELECT returning the filtered data

I am not able to wrap my head around in the working of this query which contains a subquery:-

SELECT name,salary 
FROM salary a 
WHERE  (SELECT count(*) 
        FROM salary b 
        WHERE b.salary > a.salary) = 1

I mean here, when WHERE is filtering the data out, but there is a different SELECT, FROM, WHERE respectively which is returning a data which is then compared with outer query’s WHERE condition.

so how it is associating with the outer query?

I know this is a bit intuitive but still, a flow of execution to give a proper understanding would be great.

Answer

The query in the parenthesis is called a correlated sub-query.

That is because, if you look at the first FROM, it has an alias for the salary table FROM salary a, which in this case is a.

The sub-query references the outer query in the WHERE b.salary > a.salary condition, where it checks the condition for each row in the nested sub-query.

This is how it works in large, as you might now, but the actual execution of the query, behind the scenes, might differ depending on data, available resources and many other factors.

SQL is a declarative language which means that you just tell it what you want to do with the data, through your query, and it tries to finds out what is the best way (performance-wise) to return the data.

This can be done in the form of nested loops, a hash join or an adaptive join in some newer RDBMS’s.