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
JOB MGR 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
JOB MGR 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
No comments:
Post a Comment