SUB QUERIES in Oracle

SUB QUERIES

Sub query: A sub query is a SELECT statement is embedded in a clause of another SELECT statement.
You can build powerful statements out of simple ones by using sub queries. They can be very useful when need to select rows from a table with a condition that depends on the data in the table itself:

You can place the sub query in a number of SQL clause, including

- The WHERE Clause
- The HAVING Clause
- The FROM Clause

- The sub query (inner query) executes once before the main query.

- The result of the sub query is used by the main query (Outer query).

Comparison conditions falls into two classes:
Single row Operators (<, >, =, >=, <=, <>)
Multiple row operators (IN, ANY, ALL)

Single Row Sub Queries

select ename,job from emp where deptno = (select deptno from emp where empno=7902) ;
select ename,sal from emp where sal > (select sal from emp where ename='SMITH') and
deptno = (select deptno from emp where ename='SMITH') ;

Using Group Function in a Subquery

select ename,sal from emp where sal > (select avg(sal) from emp);

Having Clause with Sub queries

select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20) ;

Using Sub Query in the From Clause

select e.ename,e.sal,e.deptno,b.salavg from emp e, (select deptno, avg(sal) salavg from emp
group by deptno) b where e.deptno=b.deptno and e.sal > b.salavg ;

Multiple Row Sub Queries

IN - Equal to any members in the list
ANY - Compare Value to each value returned by the subquery
ALL - Compare Value to every value returned by the subquery

(IN)
select ename,sal from emp where sal in (select min(sal) from emp group by sal) ;

select sal from emp where sal <>

(ANY)
select ename,sal from emp where sal <>

(ALL)
select empno,ename,job,sal from emp where sal <>
where job='MANAGER') ;

select ename,sal from emp where sal <>
select ename,sal from emp where sal <>
-----select ename,sal from emp where sal = any (select min(sal) from emp group by sal) ;
select ename,sal from emp where sal <>
select empno,ename,sal from emp where any < (1200,1600);

Correlated Sub Query

Correlated sub queries are used for row-by-row processing. Each sub query is executed once
for every row of the outer query.

SQL> SELECT ENAME, SAL, DEPTNO FROM EMP OUTER WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO=OUTER.DEPTNO ) ;

SELECT EMPNO,ENAME,DEPTNO FROM EMP A WHERE A.DEPTNO=
(SELECT DEPTNO FROM DEPT WHERE DEPTNO=A.DEPTNO);

SQL> SELECT ENAME, SAL, DEPTNO FROM EMP OUTER WHERE EXISTS (SELECT * FROM EMP WHERE DEPTNO= OUTER.DEPTNO)

SQL> SELECT ENAME, SAL, DEPTNO FROM EMP OUTER WHERE NOT EXISTS (SELECT * FROM EMP WHERE DEPTNO= OUTER.DEPTNO);

EXISTS Operator

This operator is used to check for the existence of values
This operator produces a Boolean result
It takes a subquery as an argument and evaluates it to
True, if the subquery produces any opuput and
False., if the subquery does not produce any output.

List all employees who have atleast one person reporting to them

SQL> Select empno,ename,job,deptno from emp e where EXISTS (select empno from emp where emp.mgr=e.empno) ;

List the employees details who do not manage any one.
SQL> select ename,job from emp e where NOT EXISTS (select mgr from emp where mgr=e.empno) ;