SQL Correlated Subqueries

Correlated Subqueries

SQL Correlated Subqueries are used to select data from a table referenced in the outer query. The subquery is known as a correlated because the subquery is related to the outer query. In this type of queries, a table alias (also called a correlation name) must be used to specify which table reference is to be used.

Uncorrelated subquery executes the subquery first and provides the value to the outer query, whereas correlated subquery references a column in the outer query and executes the subquery once for each row in the outer query..

The alias is the pet name of a table which is brought about by putting directly after the table name in the FROM clause. This is suitable when anybody wants to obtain information from two separate tables.

Example: SQL Correlated Subqueries

The following correlated subqueries retrive ord_num, ord_amount, cust_code and agent_code from the table orders ( 'a' and 'b' are the aliases of orders and agents table) with following conditions -

the agent_code of orders table must be the same agent_code of agents table and agent_name of agents table must be Alex,

the following SQL statement can be used:

Sample table: orders

Sample table: agents

SQL Code:

SELECT a.ord_num,a.ord_amount,a.cust_code,a.agent_code
FROM orders a
WHERE a.agent_code=(
SELECT b.agent_code
FROM agents b WHERE b.agent_name='Alex');

Output:

   ORD_NUM ORD_AMOUNT CUST_CODE  AGENT_CODE
---------- ---------- ---------- ----------
    200127       2500 C00015     A003
    200100       1000 C00015     A003

The inner of the above query returns the 'agent_code' A003.

The simplified form of above code is:

SQL Code:

SELECT a.ord_num,a.ord_amount,a.cust_code,a.agent_code
FROM orders a
WHERE a.agent_code='A003';

Pictorical Presentation:

Using EXISTS with a Correlated Subquery

We have already used the EXISTS operator to check the existence of a result of a subquery. EXISTS operator can be used in correlated subqueries also. Using EXISTS the following query display the employee_id, manager_id, first_name and last_name of those employees who manage other employees.

SQL Code:

SELECT employee_id, manager_id, first_name, last_name
FROM employees a
WHERE EXISTS
(SELECT employee_id
FROM employees b
WHERE b.manager_id = a.employee_id)

Sample table: employees

Output:

EMPLOYEE_ID MANAGER_ID FIRST_NAME           LAST_NAME
----------- ---------- -------------------- ---------------
        100            Steven               King
        101        100 Neena                Kochhar
        102        100 Lex                  De Haan
        103        102 Alexander            Hunold
        108        101 Nancy                Greenberg
        114        100 Den                  Raphaely
        120        100 Matthew              Weiss
        121        100 Adam                 Fripp
        122        100 Payam                Kaufling
        123        100 Shanta               Vollman
        124        100 Kevin                Mourgos
        145        100 John                 Russell
        146        100 Karen                Partners
        147        100 Alberto              Errazuriz
        148        100 Gerald               Cambrault
        149        100 Eleni                Zlotkey
        201        100 Michael              Hartstein
        205        101 Shelley              Higgins

Pictorial Presentation:

Using NOT EXISTS with a Correlated Subquery

NOT EXISTS is logically opposite of EXISTS operator. NOT EXISTS is used when we need to check if rows do not exist in the results returned by a subquery. Using NOT EXISTS the following query display the employee_id, manager_id, first_name and last_name of those employees who have no manager status. This query is opposite to the previous one.

SQL Code:

SELECT employee_id, manager_id, first_name, last_name
FROM employees a
WHERE NOT EXISTS
(SELECT employee_id
FROM employees b
WHERE b.manager_id = a.employee_id);

Sample table: employees

Output:

EMPLOYEE_ID MANAGER_ID FIRST_NAME           LAST_NAME
----------- ---------- -------------------- --------------
        104        103 Bruce                Ernst
        105        103 David                Austin
        106        103 Valli                Pataballa
        107        103 Diana                Lorentz
        109        108 Daniel               Faviet
        110        108 John                 Chen
        111        108 Ismael               Sciarra
        112        108 Jose Manuel          Urman
        113        108 Luis                 Popp
        115        114 Alexander            Khoo
        116        114 Shelli               Baida
        117        114 Sigal                Tobias
        118        114 Guy                  Himuro
        119        114 Karen                Colmenares
        125        120 Julia                Nayer
        126        120 Irene                Mikkilineni
        127        120 James                Landry
        128        120 Steven               Markle
        129        121 Laura                Bissot
        130        121 Mozhe                Atkinson
        131        121 James                Marlow
........
.......

Pictorial Presentation:

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Multiple Row and Column Subqueries
Next: Nested subqueries