SUBQUERIES AND EXISTS

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 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

Leave a Reply