What select is executed first in a nested subquery?

Question

For such a subquery, what select is executed first?

SELECT name, salary, dept_id
FROM employee
WHERE salary > 
    ( SELECT AVG(salary) FROM employee
       WHERE dept_no =
                  ( SELECT dept_no FROM employee 
                    WHERE last_name =
                        ( SELECT last_name FROM employee
                          WHERE salary > 50000))) ;

This: SELECT last_name FROM employee ?

Include your execute plan when you run this and it will tell you. — SS_DBA, Jan 25 '17 at 16:47
It is up to the optimizer to decide which should execute first — Pரதீப், Jan 25 '17 at 16:49
Possible duplicate of [In which sequence are queries and sub-queries executed by the SQL engine?](http://stackoverflow.com/questions/2263186/in-which-sequence-are-queries-and-sub-queries-executed-by-the-sql-engine) — Rajat Mishra, Jan 25 '17 at 16:50
Your query, as written, will break on two occasions: 1) When you have more than one employee with a salary more than 50,000 and 2) When you have more than one employee with the same last name where one of them makes more than 50,000. — Siyual, Jan 25 '17 at 16:50
to add to @siyual said, and it would break when two employees with the same last name making more than 50,000 in different departments. the issue is the = on a subquery which could return more than 1 record. — xQbert, Jan 25 '17 at 16:57

score 1 · Answer 1 · answered Jan 25 '17 at 16:50

SQL is a declarative language, not a procedural language. That is, the query does not specify the execution path, it specifies the logic for the result set. So, any of the queries could be "executed" first, depending on what the SQL optimizer decides to do.

That said, it is probably more important to understand the query logic than to understand how it is executed (at least at this stage). Your queries are all uncorrelated, so you can actually start with either the innermost or the outermost and work from there. Something like:

Get all employees whose salary
is greater than the average salary for the department
where employees with the same last name
have a salary greater than 50,000

Whether that is how the query is executed is immaterial. Something like that is what the query will return.

so I can consider this one first also: SELECT last_name FROM employee ? — GrandTim, Jan 25 '17 at 16:53
@GrandTim . . . Yes, you could think of it the other way, although it seems more confusing to me. — Gordon Linoff, Jan 26 '17 at 02:50

What select is executed first in a nested subquery?

1 Answers1