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:
-- Selecting specific columns from the orders table and aliasing it as 'a'
SELECT a.ord_num, a.ord_amount, a.cust_code, a.agent_code
-- Filtering orders based on agent_code
FROM orders a
-- Specifying the condition for filtering
WHERE a.agent_code = (
-- Selecting agent_code from agents table and aliasing it as 'b'
SELECT b.agent_code
-- Filtering agents based on agent_name
FROM agents b
-- Specifying the condition for filtering
WHERE b.agent_name = 'Alex'
);
Explanation:
- This SQL query retrieves specific columns from the "orders" table, aliasing it as 'a'.
- It filters the orders based on the agent_code.
- The subquery selects the agent_code from the "agents" table, aliasing it as 'b'.
- It filters the agents based on the agent_name being 'Alex'.
- The main query then selects orders where the agent_code matches the result of the subquery, meaning it selects orders handled by the agent with the 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:
-- Selecting specific columns from the employees table and aliasing it as 'a'
SELECT employee_id, manager_id, first_name, last_name
-- Filtering employees based on the EXISTS condition
FROM employees a
-- Specifying the condition for filtering using the EXISTS keyword
WHERE EXISTS (
-- Selecting employee_id from employees table and aliasing it as 'b'
SELECT employee_id
-- Filtering employees based on manager_id
FROM employees b
-- Specifying the condition for filtering
WHERE b.manager_id = a.employee_id
);
Explanation:
- This SQL query retrieves specific columns from the "employees" table, aliasing it as 'a'.
- It filters the employees based on the existence of records in a correlated subquery.
- The subquery selects the employee_id from the "employees" table, aliasing it as 'b'.
- It filters the employees based on whether there exists an employee with a manager_id that matches the employee_id from the outer query.
- The main query then selects employees where the subquery returns at least one record, meaning it selects employees who manage other employees.
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:
-- Selecting specific columns from the employees table and aliasing it as 'a'
SELECT employee_id, manager_id, first_name, last_name
-- Filtering employees based on the NOT EXISTS condition
FROM employees a
-- Specifying the condition for filtering using the NOT EXISTS keyword
WHERE NOT EXISTS (
-- Selecting employee_id from employees table and aliasing it as 'b'
SELECT employee_id
-- Filtering employees based on manager_id
FROM employees b
-- Specifying the condition for filtering
WHERE b.manager_id = a.employee_id
);
Explanation:
- This SQL query retrieves specific columns from the "employees" table, aliasing it as 'a'.
- It filters the employees based on the non-existence of records in a correlated subquery.
- The subquery selects the employee_id from the "employees" table, aliasing it as 'b'.
- It filters the employees based on whether there does not exist an employee with a manager_id that matches the employee_id from the outer query.
- The main query then selects employees where the subquery returns no records, meaning it selects employees who do not manage other employees.
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:
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Multiple Row and Column Subqueries
Next: Nested subqueries
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics