Nested Sub query VS Correlated Sub query

With a normal nested sub query, the inner SELECT query runs first and executes once, returning values to be used by the main query.  A correlated sub query however, executes once for each candidate row considered by the outer query.  In other words the inner query is driven by the outer query.

Steps of execution:

 Nested sub query execution:

• The inner query is executed first and finds a value
• The outer query executes once, using the value from the inner query.

Correlated Sub query execution:

• Get the candidate row (fetched by the outer query).
• Execute the inner query using the value of the candidate row.
• Use the values resulting from the inner query to test “qualify or disqualify” the candidate.
• Repeat until no candidate row remains tested.

The general format of a correlated sub query is:

select column1, column2, . . . . . . .

from table1 outer

where [column1] operator (select column1, column2,

from table2 where expr1= outer.expr2);

What are Sub Queries in ORACLE

Nesting of queries, i.e., a query which is inside another query.  Here the outer query is called the parent query, which gets a result from the inner query.  So the inner query gets executed first and based on its result the outer query gets executed. 

Note1:

• Do no add an order by clause to a sub query.
• Use single row operator with single row sub query.
• Use multiple row operators with multiple-row sub query.
• The single row sub query operators are =, >, <, <=, >=, <>, ^=, !=
• The multiple row operators are IN, ANY, ALL

Note: 2

• ‘=any’ means IN and
• ‘!=all’ means NOT IN

Note: 3

• <any means less than maximum
• >any means greater than minimum
• <all means less than minimum
• >all means greater than maximum

SUB-QUERIES:SINGLE ROW SUB QUERY

SQL> select * from emp where sal>(select sal from emp where ename='SCOTT');

    EMPNO ENAME      JOB          HIREDATE        SAL        DEPTNO

--------- ---------- ---------    --------- --------- --- ---------

     7839 KING       PRESIDENT     17-NOV-81      5000         10

SQL >select * from emp where sal>=(select sal from emp where ename='SCOTT');

     EMPNO ENAME      JOB         HIREDATE        SAL         DEPTNO

--------- ---------- ---------  --------- ---------        ---------

     7788 SCOTT      ANALYST     09-DEC-82      3000          20

     7839 KING       PRESIDENT   17-NOV-81      5000          10

     7902 FORD       ANALYST     03-DEC-81      3000          20

SQL >select ename,job from emp where job=(select job from emp where empno=7369);

ENAME      JOB

---------- ---------

SMITH      CLERK

ADAMS      CLERK

JAMES      CLERK

MILLER     CLERK

SQL > select ename,job from emp where job=(select job from emp where empno=7369)and sal>(select sal from emp where empno=7876);

ENAME      JOB

---------- ---------

MILLER     CLERK

 

SQL >select * from emp where job='CLERK';

    EMPNO  ENAME      JOB       HIREDATE        SAL         DEPTNO

 --------- - --------- ---------  --------- ---------       --------------------

     7369       SMITH      CLERK       17-DEC-80       800        20

     7876       ADAMS     CLERK       12-JAN-83      1100      20

     7900       JAMES      CLERK       03-DEC-81       950        30

     7934       MILLER     CLERK       23-JAN-82      1300      10

SUB-QUERIES: MULTIPLE ROW SUB QUERY

SQL >select empno,ename,sal from emp where sal=any(select sal from emp,salgrade

 where emp.sal between salgrade.losal and salgrade.hisal);

    EMPNO ENAME            SAL

--------- ---------- ---------

     7369 SMITH            800

     7900 JAMES            950

     7876 ADAMS           1100

     7521 WARD            1250

     7654 MARTIN          1250

     7934 MILLER          1300

     7844 TURNER          1500

     7499 ALLEN           1600

     7782 CLARK           2450

     7698 BLAKE           2850

     7566 JONES           2975

     7788 SCOTT           3000

     7902 FORD            3000

     7839 KING            5000

14 rows selected.

SQL >select empno,ename,sal from emp where sal !=all (select sal from emp,salgrade    where emp.sal between salgrade.losal and salgrade.hisal);

no rows selected