SQL Exists

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