Follow us: Connect on YouTube 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 :

3 comments:

  1. Hi There,


    Thank you! Thank you! Thank you! Your blog was a total game changer!

    I try to calculate MAT (Moving Annual Total) in my Finance Multi-dimensional cube with the below. It is not working or not calculating. But when I replace the Aggregate with Sum, it worked but wrong numbers. Need your help on finding the work around. I tried couple of options from internet

    Once again thanks for your tutorial.


    Thanks,
    David

    ReplyDelete
  2. Hello There,


    Grazie! Grazie! Grazie! Your blog is indeed quite interesting around Exists function is a test for existence.! I agree with you on lot of points!

    how to get the first non space character from a string in Oracle Could you help me on this?
    Anyways great write up, your efforts are much appreciated.


    Many Thanks,
    Daniel

    ReplyDelete
  3. Hello Dayakar,

    Three cheers to you ! Hooray!!! I feel like I hit the jackpot on SQL Exists !

    I am trying to write a PLSQL block that firstly..

    grabs the table name from all_tables where owner='rob1' and rownum =1

    I then want to assign this table_name to a variable.

    I then want to count the number of rows of this table and print to the screen.

    I have pasted my code below. Are you able to reference a variable withing a sql statement as I have?
    DECLARE
    TABLE_HOLDER VARCHAR2(200);
    COUNT_OF_ROWS NUMBER;

    BEGIN

    SELECT TABLE_NAME INTO TABLE_HOLDER FROM ALL_TABLES
    WHERE OWNER ='ROB1'AND ROWNUM=1;
    DBMS_OUTPUT.PUT_LINE(TABLE_HOLDER);

    SELECT COUNT(*)INTO COUNT_OF_ROWS FROM TABLE_HOLDER;
    DBMS_OUTPUT.PUT_LINE(COUNT_OF_ROWS);
    END;

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Kind Regards,
    Preethi.

    ReplyDelete