10 Sub Queries

sub-queries *one of the advanced query techniques in sql *you can use the results of one query as part of another

query *subqueries let you break a query down into pieces and then

put the pieces together *subqueries are used in the where or having clause *example

:

1. list the employees belonging to the department of ‘miller’. 2. list all the employees who have the same job as `scott’. *guidelines for using subqueries : *the subquery must be enclosed in the parenthesis *you can reference columns from the outer query in the

inner query *columns common to both queries are to be prefixed with their table names *oracle generates error if *the subquery returns no value *the subquery returns more than one values *special operators can be used in these cases *these operators are used in queries where the inner query

returns more than one value *operators are *in or not in *exists *any *all

in

*in

or not in operator : *example : *list the details of employees in department 10 who have

the same job as in department 30.

exists

*exists operator : *existence of rows in the inner query may be used to

qualify rows of an outer query *this operator produces a boolean result *it takes a subquery as an argument and evaluates it to true if it produces any output or false, if it does not

*examples

:

i.list the employee details if and only if more than 2 employees are present in department 20.

sol.

sol.

ii.find the employees who have at least one person reporting to them. select empno,job,deptno from emp e1 where exists (select ‘x’ from emp where mgr = e1.empno) iii.find all the departments who don’t have any employees select deptno, dname from dept d1 where not exists (select ‘x’ from emp where deptno = d1.deptno)

any, all

*any, all operator : *used alongwith relational operators in subqueries *if a subquery returns more than one row and the comparison other than equality is required *examples

:

1. list the employee names whose salary is equal to the salary of an employee belonging to department 30. 2. list the employee names whose salary is greater than the salary of all employees belonging to department 30.

sub-queries *objectives : *explain & demonstrate nested sub-queries *explain & demonstrate co-related sub-queries

nested queries

*the result of one query is dynamically substituted in the

condition of another *sql first evaluates the inner query within the where clause *the nested select clause in the inner query can in turn have another nested select clause

co-related sub-queries

*co-related sub-queries : *a co-related sub-query refers to a column selected by the

main query *sometimes table used in inner query refers to the same table used in outer query. table aliases are used in such cases *having clause is also used in some cases

*examples : *1. list the employees who earn more than the average

salary in their own department. *2. list employee and his managers details whose salary is greater than their managers salary. *3. list employees whose salary is highest in their department. *4. find the employees who earn more than the average

sal in their dept. *sol. select empno, sal ,deptno from emp e1 where sal > (select avg(sal) from emp where deptno = e1.deptno)