Stepping through Sub-Queries in Oracle - The simplest sub-query in Oracle
(Page 2 of 5 )
Before explaining sub-queries, let us consider the following scenario:
SQL> SELECT empno,ename,sal,deptno FROM emp
WHERE sal = 5000;
EMPNO ENAME SAL DEPTNO
--------- ---------- ----------- ------------
7839 KING 5000 10
1 rows selected
Let us modify the above statement as follows:
SQL> SELECT empno,ename,sal,deptno FROM emp
WHERE sal = (SELECT 5000 FROM dual);
I already explained the “dual” table in the previous section. In the above statement, I have two SELECT statements as part of a single command. The following is the order in which the above statement gets executed:
- The innermost query gets executed first.
- In this case, the query “select 5000 from dual” gets executed first.
- Once the innermost query gets executed, it returns a value to the immediate outer query. In this case, it is 5000.
- The entire innermost query gets replaced with the new value returned by it. In this case, the outer query virtually becomes “select empno, ename, sal, deptno from emp where sal = 5000.”
- And finally, the outer query gets executed, which retrieves KING’s details.
In the above case, I used a SELECT query as part of another SELECT query; thus it is called a “sub-query.” You can even modify the above statement to include an expression as follows:
SQL> SELECT empno,ename,sal,deptno FROM emp
WHERE sal = (SELECT 2000+3000 FROM dual);
The above statement first evaluates “2000+3000” (which results in 5000) as part of executing the inner query. Based on the returned value (which is 5000), the outer query gets executed.
The next section will show you a few simple and practically used sub-queries.
Next: A sub-query with aggregate functions (or group functions) in Oracle >>
More Oracle Articles
More By Jagadish Chatarji
