Open In App
Related Articles

SQL Correlated Subqueries

Improve Article
Save Article
Like Article

 Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every row of the outer query.
A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.

SELECT column1, column2, ....
FROM table1 outer
WHERE column1 operator
                    (SELECT column1, column2
                     FROM table2
                     WHERE expr1 = 

A correlated subquery is one way of reading every row in a table and comparing values in each row against related data. It is used whenever a subquery must return a different result or set of results for each candidate row considered by the main query. In other words, you can use a correlated subquery to answer a multipart question whose answer depends on the value in each row processed by the parent statement. 

Nested Subqueries Versus Correlated Subqueries :

With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to be used by the main query. A correlated subquery, however, executes once for each candidate row considered by the outer query. In other words, the inner query is driven by the outer query.

NOTE: You can also use the ANY and ALL operator in a correlated subquery. EXAMPLE of Correlated Subqueries : Find all the employees who earn more than the average salary in their department.

SELECT last_name, salary, department_id
 FROM employees outer
 WHERE salary >
                (SELECT AVG(salary)
                 FROM employees
                 WHERE department_id =
                        outer.department_id group by department_id);

Other use of correlation is in UPDATE and DELETE


UPDATE table1 alias1
 SET column = (SELECT expression 
               FROM table2 alias2
               WHERE alias1.column =

Use a correlated subquery to update rows in one table based on rows from another table.


DELETE FROM table1 alias1
 WHERE column1 operator
               (SELECT expression
                FROM table2 alias2
                WHERE alias1.column = alias2.column);

Use a correlated subquery to delete rows in one table based on the rows from another table.

Using the EXISTS Operator :

The EXISTS operator tests for existence of rows in the results set of the subquery. If a subquery row value is found the condition is flagged TRUE and the search does not continue in the inner query, and if it is not found then the condition is flagged FALSE and the search continues in the inner query.
EXAMPLE of using EXIST operator :
Find employees who have at least one person reporting to them.

SELECT employee_id, last_name, job_id, department_id
FROM employees outer
FROM employees
WHERE manager_id =


EXAMPLE of using NOT EXIST operator :
Find all departments that do not have any employees.

SELECT department_id, department_name
FROM departments d
FROM employees
WHERE department_id
= d.department_id);



Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our Complete Interview Preparation Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our Complete Interview Preparation course.

Last Updated : 11 Dec, 2022
Like Article
Save Article
Similar Reads