sql / intermediate
Snippet
Correlated Subqueries for Row-by-Row Comparison
A correlated subquery evaluates once for every row processed by the outer query. By referencing columns from the outer table (e1.department_id), the subquery dynamically computes an aggregate threshold specific to that row's context, such as finding employees who earn more than their own department's average salary.
snippet.sql
sql
1
2
3
4
5
6
7
SELECT e1.emp_id, e1.department_id, e1.salaryFROM employees e1WHERE e1.salary > (SELECT AVG(e2.salary)FROM employees e2WHERE e2.department_id = e1.department_id);
Breakdown
1
SELECT e1.emp_id, e1.department_id, e1.salary
Specifies the outer query columns to retrieve from the primary table alias.
2
FROM employees e1
Defines the primary table and assigns the alias e1 for row-by-row correlation.
3
WHERE e1.salary > (
Filters outer rows where the current employee salary exceeds the subquery scalar output.
4
SELECT AVG(e2.salary)
Computes the average salary within the nested inner subquery for comparison.
5
FROM employees e2
Defines the inner query target table with alias e2.
6
WHERE e2.department_id = e1.department_id
Correlates each inner calculation to the department_id of the current outer row.
7
);
Closes the subquery expression.