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
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
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.
0 Responses to “ORACLE SQL TUTORIAL: CORRELATED SUBQUERIES AND EXISTS”
Post a Comment