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.
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:

Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
Previous: Multiple Row and Column Subqueries
Next: Nested subqueries
SQL: Tips of the Day
How to calculate age (in years) based on Date of Birth and getDate()?
DECLARE @dob datetime SET @dob='1992-01-09 00:00:00' SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal ,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound ,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc
Output:
ID Name AGE DOB 1 John 17 1992-01-09 00:00:00 2 Sally 50 1959-05-20 00:00:00
Ref: https://bit.ly/3PyIJvQ
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework