Multi Row Sub Query

=>    If sub query returns more than one value then the sub query is called 'Multi Row Sub Query'.

    Syntax:
    =>    Select <collist> from <tableName> where colname OP ( Select statement);

=> OP can be { IN, NOT IN, ALL, EXISTS, ANY }

    Examples:

    1. Get the 5th record from emp ?
    =>    Select * from emp where empno in ( select DECODE( rownum, 5, empno) from emp );
       
    2. Display Alternate records from emp ?
    =>    Select * from emp where empno in ( select DECODE( MOD(rownum,2) , 1, empno ) from emp );

    3. Display names of employee's who are earning MIN and MAX salary ?
    =>    Select * from emp where sal IN ( select MAX(sal) from emp UNION select MIN(sal) from emp);