SQL 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
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');
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:
SELECT a.ord_num,a.ord_amount,a.cust_code,a.agent_code FROM orders a WHERE a.agent_code='A003';
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.
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
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
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.
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
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 ........ .......
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Multiple Row and Column Subqueries
Next: Nested subqueries
SQL: Tips of the Day
UPDATE and REPLACE part of a string:
UPDATE dbo.xyz SET Value = REPLACE(Value, '123', '') WHERE ID <=4
If the column to replace is type text or ntext you need to cast it to nvarchar
UPDATE dbo.xyz SET Value = REPLACE(CAST(Value as nVarchar(4000)), '123', '') WHERE ID <=4
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook