Subquery in Oracle

ORACLE SUBQUERIES

A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements.
 These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.


Simple Oracle subquery:
----------------------
A simple subquery is evaluated once for each table.

SQL> select empno,ename,job,sal,deptno from emp where
     deptno in (select deptno from dept
where loc = 'New York');


We can use sub queries in From caluse. This type of queries is called InLine Views.

Example:
-------

SQL> Select ename, deptno from
     (select ename, deptno from emp
where job = 'CLERK' )
     where deptno > 20;


We can even replace a column name with sub queries.

Example:
-------

SQL> select ename,(select max(sal) from emp) "maxsal" ,
            sal,((select max(sal) from emp ) - sal ) difference"  from emp;

Sub-Queries with multiple columns in Oracle:
-------------------------------------------

SQL> SELECT * FROM emp WHERE (sal,mgr) = (3000,7566);


SQL>SELECT * FROM emp
    WHERE (sal,mgr) =
    (SELECT sal,mgr FROM emp
             WHERE sal = (SELECT MIN(sal) FROM EMP
                          WHERE sal > (SELECT MIN(sal) FROM emp)))

The above returns all the employees who are earning the same salary and working under
the same manager of the employee earning the second least salary.



SELECT * FROM scott.emp WHERE empno IN   (SELECT empno FROM scott.emp
                          WHERE sal =  (SELECT MAX(sal) FROM scott.EMP WHERE sal <
                                   (SELECT MAX(sal) FROM scott.emp )));

The above query gives us all the employees earning the second highest salary.


Correlated Oracle subquery:
-------------------------

A correlated Oracle subquery is evaluated once FOR EACH ROW
as opposed to a normal subquery which is evaluated only once for each table.

Example:
-------
select all employees whose salary is less than the average of all the employees' salaries in the same department.

SQL> select ename ,sal ,deptno from emp a where
     a.sal < (select avg(sal) from emp b
          where a.deptno = b.deptno)
     order by deptno;


Using a correlated subquery in an update statement:
-------------------------------------------------

SQL>  UPDATE emp a
      set sal = (select avg(sal)
                from emp b
       where
       a.deptno = b.deptno)
      where sal <
  (select avg(sal) from emp c
  where a.deptno = c.deptno);


Using a correlated subquery in an Delete statement:
-------------------------------------------------
delete the highest earning employees in each department.

SQL>  delete from emp a where
      a.sal = (select max(sal) from emp b
 where a.deptno = b.deptno);


Ur's
AmarAlam