1/28/16

SUBQUERIES AND EXISTS

SUBQUERIES


Nesting of queries, one within the other is termed as a subquery. A statement containing a subquery is called a parent query.
Subqueries are used to retrieve data from tables that depend on the values in the table itself.


TYPES


Ø  Single row subqueries

Ø  Multi row subqueries

Ø  Multiple subqueries

Ø  Correlated subqueries


SINGLE ROW SUBQUERIES


In single row subquery, it will return one value.


Ex:

SQL> select * from emp where sal > (select sal from emp where empno = 7566);


EMPNO
ENAME
JOB
MGR
HIREDATE
SAL  COMM
DEPTNO
----------
----------
--------- ----------

------------ ------- ---------- ----------
7788
SCOTT
ANALYST
7566
19 -APR-87  3000
20
7839
KING
PRESIDENT
17 -NOV-81 5000
10
7902
FORD
ANALYST
7566
03 -DEC-81
3000
20


MULTI ROW SUBQUERIES


In multi row subquery, it will return more than one value. In such cases we should include operators like any, all, in or not in between the comparision operator and the subquery.





Ex:

SQL> select * from emp where sal > any (select sal from emp where sal between 2500 and 4000);

EMPNO
ENAME
JOBMGR   HIREDATE
SAL  COMM
DEPTNO
----------
----------
--------- ----------
-----------  -------- ---------- ----------
7566
JONES
MANAGER
7839 02-APR-81
2975
20
7788
SCOTT
ANALYST
7566 19-APR-87
3000
20
7839
KING
PRESIDENT
17-NOV-81
5000
10
7902
FORD
ANALYST
7566 03-DEC-81
3000
20


SQL> select * from emp where sal > all (select sal from emp where sal between 2500 and 4000);

EMPNO
ENAME
JOBMGR
HIREDATE   SAL
COMM DEPTNO
----------
----------
--------- ----------
------------- ------ ---------- ----------
7839
KING
PRESIDENT
17  -NOV-81 5000
10


MULTIPLE SUBQUERIES


There is no limit on the number of subqueries included in a where clause. It allows nesting of a query within a subquery.

Ex:

SQL> select * from emp where sal = (select max(sal) from emp where sal < (select max(sal) from emp));

EMPNO
ENAME  JOB
MGR
HIREDATE
SAL  COMM
DEPTNO
----------
----------
--------- ----------

------------
------- ---------- ----------
7788
SCOTT
ANALYST 7566
19  -APR-87
3000
20
7902
FORD
ANALYST
7566
03 -DEC-81
3000
20


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





0 comments:

Post a Comment