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);