Stepping through Sub-Queries in Oracle - An example of a nested sub-query (or multi-level sub-query)
(Page 5 of 5 )
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
