The simplest sub-query in Oracle - Oracle

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.