Stepping through Sub-Queries in Oracle - Designing sub-queries to deal with more than one table (or different tables)
(Page 4 of 5 )
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
Next: An example of a nested sub-query (or multi-level sub-query) >>
More Oracle Articles
More By Jagadish Chatarji
