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
---------- ---------- --------- ---------- ------------ ------- ---------- ----------zz
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(empno) from emp group by deptno having
count(empno) > 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