Oracle Subqueries with examples

SUBQUERIES AND EXISTS

SUBQUERIES

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

A statement containing a subquery is called a parent query.

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

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

EXISTS

Exists function is a test for existence. This is a logical test for the return of rows from a query.

Ex:

     Suppose we want to display the department numbers which has more than 4 employees.

     SQL> select deptno,count(*) from emp group by deptno having count(*) > 4;

   DEPTNO   COUNT(*)

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

       20             5

       30             6

     From the above query can you want to display the names of employees?

      SQL> select deptno,ename, count(*) from emp group by deptno,ename having count(*) > 4;

     no rows selected

     The above query returns nothing because combination of deptno and ename never return     

     more than one count.

     The solution is to use exists which follows.

      

      SQL> select deptno,ename from emp e1 where exists (select * from emp e2

             where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename) > 4) order by

             deptno,ename;

 

    DEPTNO   ENAME

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

        20            ADAMS

        20            FORD

        20            JONES

        20            SCOTT

        20            SMITH

        30            ALLEN

        30            BLAKE

        30            JAMES

        30            MARTIN

        30            TURNER

        30            WARD

NOT EXISTS

SQL> select deptno,ename from emp e1 where not exists (select * from emp e2

        where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename) > 4) order by

        deptno,ename;

   DEPTNO ENAME

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

       10             CLARK

       10             KING

       10             MILLER