An example of a nested sub-query (or multi-level sub-query) - Oracle

This is the first article in a series concentrating on working with sub-queries in Oracle. Sub-queries really have tremendous depth. In this series I will show you several scenarios where they can be efficiently used to retrieve information from Oracle databases.

TABLE OF CONTENTS:

By: Jagadish Chatarji

Poor Best

Rating:

/ 28

November 07, 2006

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

Let us consider that I would like to retrieve the department name of the highest paid employee. Let us write the question in a meaningful manner and identify the steps as follows:

From the above figure, you have three steps to go through with the query. The following is the order you must follow (based on the above figure):

Find the highest salary (1).
Based on the value you get, retrieve the department number belonging to that salary (2).
Based on the value you get, find the department name belonging to that department number (3).

The following is the statement which finds the highest salary:

SELECT MAX(sal) FROM emp

To retrieve a department number based on a given salary (say 2000), the query would be as follows:

SELECT deptno FROM emp

WHERE sal = 2000

To retrieve a department name based on a given department number (say 20), the query would be as follows:

SELECT dname FROM dept

WHERE deptno = 20

Combining all of the above queries according to the order given above, the new query would be as follows:

SQL> SELECT dname FROM dept

WHERE deptno = (SELECT deptno FROM emp

WHERE sal = (SELECT MAX(sal) FROM EMP));

You can observe the following figure to understand how the execution takes place. You can also observe the underlined columns on how they relate logically:

Now, let us walk through its execution:

The innermost query gets executed first.
In this case, the query “select max(sal) from emp” gets executed first. It retrieves the highest salary. In this case it would be 5000.
The entire innermost query gets replaced with the new value returned by it. In this case, the immediate outer query virtually becomes “select deptno from emp where sal = 5000.” Once this query gets executed, it returns a department number, which is 10 in this case.
And finally, the outermost query virtually becomes “select dname from dept where deptno = 10,” which retrieves KING’s department details.

Any bugs, doubts, suggestions, feedback etc. are highly appreciated at http://jagchat.spaces.live.com