Saturday, October 18, 2014

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






No comments:

Post a Comment