Sub Queries

SUBQUERIES

1     Nesting of queries, one within the other is termed as a subquery.

2     A statement containing a subquery is called a parent query.

3     Subqueries are used to retrieve data from tables that depend on the values in the table itself.

TYPES

1     Single row subqueries

2     Multi row subqueries

3     Multiple subqueries

4     Correlated subqueries

1.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

2.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

3. 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

4. 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