Correlated Subqueries for Comparative Analysis

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
sql
SELECT e1.employee_id, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

Breakdown

SELECT e1.employee_id, e1.salary

Selects the ID and salary from the primary employee table alias.

WHERE e1.salary > (SELECT AVG(e2.salary)...)

Filters rows where the salary exceeds the result of the inner aggregate function.

WHERE e2.department_id = e1.department_id

The correlation step: links the subquery's department to the current row of the outer query.

Previous snippet Next snippet

From your library

Correlated Subqueries for Comparative Analysis

Related