Friday, October 28, 2011

ORACLE SQL TUTORIAL: CORRELATED SUBQUERIES AND EXISTS



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

1 Responses to “ORACLE SQL TUTORIAL: CORRELATED SUBQUERIES AND EXISTS”

Sridevi K said...
October 8, 2016 at 12:58 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.