sql / expert
Snippet
Correlated Subqueries for Comparative Analysis
A correlated subquery refers to columns in the outer query. This example identifies employees who earn more than the average salary within their specific department, requiring the subquery to execute for every row evaluated by the outer statement.
snippet.sql
1
2
3
4
5
6
7
SELECT e1.employee_id, e1.salaryFROM employees e1WHERE e1.salary > (SELECT AVG(e2.salary)FROM employees e2WHERE e2.department_id = e1.department_id);
Breakdown
1
SELECT e1.employee_id, e1.salary
Selects the ID and salary from the primary employee table alias.
2
WHERE e1.salary > (SELECT AVG(e2.salary)...)
Filters rows where the salary exceeds the result of the inner aggregate function.
3
WHERE e2.department_id = e1.department_id
The correlation step: links the subquery's department to the current row of the outer query.