SUBQUERY @ CO-RELATED SUBQUERY

 SUBQUERY:

Ø  Query within Query

Ø  First inner query executes & out query executes based on the result of inner query

Ø  Max of 15 sub queries can be coded

Ø  To simplify sub queries, logic can be built with combination of COBOL + SQL statements

To retrieve second maximum  salary  from emp table:

           

            Select max(sal) from emp where sal <(select max(sal) from emp)

To retrieve third maximum salary from emp table:

            Select max(sal) from emp

where sal < (select max(sal) from emp

                                                      Where sal  < (select max(sal) from emp))

CO-RELATED SUBQUERY:

Ø  For every row of outer query, inner query must executes at least once

Ø  First outer query executes & then inner query executes

Ø  Practical examples : to fine top 2,3 or n salaries

Select a. sal from emp a

where 0 = (select count(*) from emp b

                                                             Where a.sal < b.sal)

                                                               

0        -- max

1        – 2^nd max

2        – 3^rd max

3        – 4^th max