Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

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
Share this article :

0 comments:

Post a Comment