HomeOracle Page 4 - Stepping through Sub-Queries in Oracle
Designing sub-queries to deal with more than one table (or different tables) - 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.
Let us consider that I would like to retrieve KING's department name. All department names are available in the table “dept,” which is quite different from “emp” table. Let us write the question in a meaningful manner and identify the steps as follows:
From the above figure, you have two steps to go through with the query. The following is the order you must follow (based on the above figure):
Find KING's department number (1).
Based on the value you get, retrieve the department details like dname, loc. etc belonging to that department number (2)
The following is the statement which retrieves KING's department number:
SELECT deptno FROM emp WHERE ename = ‘KING’
To retrieve department details based on a given department number (say 30), the query would be as follows:
SELECT dname FROM dept
WHERE deptno = 30
Just replace the value 30 with the query that gives you KING's department number. The complete statement would be as follows:
SQL> SELECT dname FROM dept
WHERE deptno = (SELECT deptno FROM emp WHERE ename=’KING’);
Now, let us walk through its execution:
The innermost query gets executed first.
In this case, the query “select deptno from emp where ename=’king’” gets executed first. It retrieves KING's department number.
Once the innermost query gets executed, it returns a value to the immediate outer query. In this case, it is 10.
The entire innermost query gets replaced with the new value returned by it. In this case, the outer-query virtually becomes “select dname from dept where deptno = 10.”
And finally, the outer query gets executed, which retrieves KING’s department details