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