RSS Feed

SQL Correlated Subquery

SQL inline View subquery SQL Subqueries SQL Scalar subquery

When you reference a column from the table in the parent query in the subquery, it is known as a correlated subquery. For each row processed in the parent query, the correlated subquery is evaluated once.

While processing Correlated subquery:
  • The first row of the outer query is fetched.
  • The inner query is processed using the outer query’s value or values.
  • The outer query is processed using the inner query’s value or values.
  • This process is continued until the outer query is done.
Example 1 of Correlated subquery

Display all employees who have switched jobs at least twice.

SELECT e.last_name, e.salary, e.job_id
FROM employees e
WHERE 2 <= (SELECT Count(*)
FROM job_history j
WHERE j.employee_id = e.employee_id);

Example 2 of Correlated subquery

SELECT S.Number, S.Name
FROM Salesman S
WHERE S.Number IN(SELECT C.Salesman
FROM Customer C
WHERE C.Name = S.Name);