Sub Queries

SUB-QUERIES

===========

* Sub-query means query within a query

* It is also called as nested query

Syntax: -

======

select             -------

from               -------

where             -------

          (

                        select             ---------------

                       from               ---------------

                        where             ---------------

          )

/

ü  Here the output of inner query is passed as input to the outer query.

ü  To write Sub-query we should have a common column between the tables.

ü  The common column should have same data type but it can have different name.

ü  The result to be displayed on the screen should be written as the outer query.

Eg:-

[1] display the emps from accounting dept.

 SQL> select *

         from emp

         where deptno = (select deptno from dept where Dname='ACCOUNTING' );

[2] display dname of 'BLAKE'.

 SQL> select dname

          from dept where deptno = (select deptno from emp where ename ='BLAKE');

[3]DISPLAY ALL THE PERSONS LIVING IN NEWYORK

  SQL> select *

          from emp where deptno =   (select deptno from dept where loc='NEWYORK');

[4]DISPLAY THE EMPLOYEES WORKING IN ACCOUNTING AND RESEARCH DEPT.

  SQL>  select *

           from emp

            where deptno IN (select deptno from dept

                                  where dname IN ('ACCOUNTING', 'RESEARCH'));

[5] DISPLAY THE EMP WHO HAS JOINED FIRST.

  SQL> SELECT *

           FROM EMP

                    WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP);

[6] LIST THE ENAME WHO IS GETTING FIRST HIGHEST SALARY.

   SQL> SELECT *

            FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP);               

[7] DISPLAY THE DNAME WHICH IS GETTING HIGHEST SALARY.

   sql> SELECT * 

            FROM DEPT

            WHERE DEPTNO =

                       (SELECT DEPTNO

                         FROM EMP

                         WHERE SAL =    

                           (SELECT MAX(SAL) FROM EMP)

                        );

[8] DISPLAY THER SECOND HIGHEST SALARY.

  sql> SELECT MAX(SAL)

            FROM EMP

            WHERE SAL <

                        (SELECT MAX(SAL)

                         FROM EMP

                        );

[9] DISPLAY FOR FOURTH HIGHEST SALARIES

   SQL>  SELECT MAX(SAL) FROM EMP WHERE SAL <

           ( SELECT MAX(SAL) FROM EMP WHERE SAL <

           ( SELECT MAX(SAL) FROM EMP WHERE SAL <

           (  SELECT MAX(SAL) FROM EMP )));

[10] LIST THE EMP REPORTING TO BLAKE.

  SQL>  SELECT *

            FROM EMP

            WHERE MGR=

                        (SELECT EMPNO

                        FROM EMP

                        WHERE ENAME ='BLAKE');

[11] DISPLAY THE EMPS WHO DO NOT HAVE ANY REPORTING MANAGERS.

  SQL> SELECT *

            FROM EMP

            WHERE MGR NOT IN

                       (SELECT EMPNO

                                    FROM EMP

                                    WHERE MGR  IS NOT NULL);

========================================================

PAIRED COLUMNS

============

SELECT       ---------

FROM          ---------

WHERE(COL1, COL2) IN (SELECT COL4, COL5 FROM ------):

Oracle will understand as  "WHERE COL1=COL4 AND COL2=COL5"

EG:-

====

Display emps earning highest salary in their respective depts.

select *

from emp

where (deptno, sal) IN (select deptno, max(sal)

                                    from emp

                                    group by deptno

                               )

/

=============================================================================

LIMITATIONS OF SUB QUERIES: -

============================

* ONLY UPTO 256 LEVEL OF SUBQUERIES IS POSSIBLE.

* THE ORDER BY CLAUSE SHOULD NOT BE USED IN INNER QUERIES (SHOULD BE USED IN THE OUTERMOST QUERY).

* WE CANNOT FETCH THE DATA FROM TWO TABLES SIMULTANEOUSLY. THIS CAN BE OVERCOME BY USING JOINS.