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