sql / intermediate
Snippet
Filtering Rows with Scalar Correlated Subqueries
A scalar correlated subquery executes once for each candidate row processed by the outer query. It computes the average salary for the current employee's specific department dynamically, allowing context-aware filtering.
snippet.sql
sql
1
2
3
4
5
6
7
SELECT e1.employee_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.employee_id, e1.department_id, e1.salary
Specifies the columns to return from the primary employees table alias e1.
2
FROM employees e1
Defines the outer query source table and assigns the table alias e1.
3
WHERE e1.salary > (
Filters rows where the employee salary is greater than the computed departmental average.
4
SELECT AVG(e2.salary)
Calculates the average salary for comparison within the inner subquery.
5
WHERE e2.department_id = e1.department_id
Correlates inner table alias e2 with outer row department_id from e1.