SQL Subqueries

A form of an SQL statement that appears inside another SQL statement, also termed as Nested Query

The statement containing a sub-query is called a parent statement

The parent statement uses the rows returned by the sub-query

TYPES

Single row subqueries

Multi row subqueries

Multiple subqueries

Correlated subqueries

SINGLE ROW SUBQUERIES

In single row subquery, it will return one value.

Ex:

    SQL> select * from emp where sal > (select sal from emp where empno = 7566);

     EMPNO   ENAME      JOB        MGR    HIREDATE    SAL   COMM     DEPTNO

       ---------- ---------- --------- ---------- ------------  ------- ---------- ----------

      7788         SCOTT   ANALYST    7566   19-APR-87   3000            20

      7839         KING     PRESIDENT            17-NOV-81  5000             10

      7902         FORD    ANALYST     7566   03-DEC-81   3000            20

MULTI ROW SUBQUERIES

In multi row subquery, it will return more than one value. In such cases we should include operators like any, all, in or not in between the comparision operator and the subquery.

Ex:

     SQL> select * from emp where sal > any (select sal from emp where sal between 2500 and

             4000);

     EMPNO    ENAME      JOB      MGR     HIREDATE   SAL   COMM     DEPTNO

    ---------- ---------- --------- ---------- -----------   -------- ---------- ----------

      7566         JONES   MANAGER   7839 02-APR-81   2975                   20

      7788         SCOTT   ANALYST    7566 19-APR-87    3000                   20

      7839         KING     PRESIDENT          17-NOV-81   5000                   10

      7902         FORD    ANALYST     7566 03-DEC-81    3000                   20

       SQL> select * from emp where sal > all (select sal from emp where sal between 2500 and

              4000);

 

     EMPNO      ENAME    JOB       MGR     HIREDATE     SAL    COMM  DEPTNO

     ---------- ---------- --------- ---------- -------------  ------ ---------- ----------

      7839         KING     PRESIDENT            17-NOV-81  5000                    10

MULTIPLE SUBQUERIES

There is no limit on the number of subqueries included in a where clause. It allows nesting of a query within a subquery.

Ex:

     SQL> select * from emp where sal = (select max(sal) from emp where sal < (select

              max(sal) from emp));

     EMPNO      ENAME   JOB      MGR       HIREDATE   SAL   COMM     DEPTNO

     ---------- ---------- --------- ---------- ------------  ------- ---------- ----------

      7788         SCOTT   ANALYST  7566    19-APR-87   3000                    20

     7902          FORD    ANALYST   7566    03-DEC-81   3000                    20

CORRELATED SUBQUERIES

A Subquery is evaluated once for the entire parent statement where as a correlated subquery is evaluated once for every row processed by the parent statement.

Ex:

     SQL> select distinct deptno from emp e where 5 <= (select count(ename) from emp where e.deptno = deptno);

    DEPTNO

    ----------

        20

        30