SQL nested queries execution order

Question

The following query is done for oracle's HR schema. I would like to know step by step which instruction gets executed first and most of all, which SELECT statement is evaluated 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))) ;

PS: I know that this query may not be valid, but that's not the point. What I would like to know is the order in which the instructions would be executed.

score 1 · Accepted Answer · answered Dec 17 '13 at 13:02

Please check the explain plan of the query it will give the details as to how oracle executes the query.

explain plan for <sql query>
select * from table(dbms_xplan.display);

Refer to http://www.dwbiconcepts.com/database/22-database-oracle/26-oracle-query-plan-a-10-minutes-guide.html for more details on explain plan.

SQL nested queries execution order

1 Answers1