Subquery in oracle SQL

Introduction:

  Ø A Subquery or inner query is a query within another SQL query and embedded within the WHERE clause.

  Ø Subqueries are used with the SELECT, INSERT, UPDATE, DELETE Statements along with the SQL operators like (=, <, >, >=, <=, IN, BETWEEN).

Types of Subquery:

Diagram representation

Non correlated subquery:

ØIn terms of non correlated subquery the inner query executed or valuated and the result of the inner query is as an input to the outer query.

Example:

1. SELECT party_name,party_id

     FROM hz_parties hp

    WHERE party_id IN (SELECT party_id

                       FROM hz_cust_accounts hca)

Output:

 Correlated subquery:

Ø The inner query is dependent on the outer query, which means both the inner and outer query executed side by side.

Example: 1

Select max(sal) from emp ;

   Reference:  Maximum salary of the employee is 5000

1. SELECT *

     FROM emp a

    WHERE  1 < (SELECT COUNT (*)

                                     FROM emp b

                                   WHERE b.sal >= a.sal)

Output:

Example: 2

1. SELECT *

     FROM emp a

    WHERE job = 'CLERK' AND 1 <= (SELECT COUNT (*)

                                     FROM emp b

                                   WHERE b.sal >= a.sal) 

Output:

The way of correlated sub query works:

Step: 1

Get candidate key from Outer query.

Step: 2

Execute the inner query using the candidate key value.

Step: 3

Use values from inner query to qualify or disqualify candidate row.

Example: 3

Find out the department which has an employee.

1. SELECT *

     FROM dept d

    WHERE  EXISTS (SELECT 1

                    FROM emp e

                   WHERE e.deptno = d.deptno);

Output:

 

 

 

Share: