subquery

Subquery in the WHERE Clause

Filter employees whose salary exceeds the average salary of their department.

Overview

A subquery in WHERE produces values used for filtering. When the subquery references the outer query (correlated), it runs once per outer row. Here we keep only employees paid above their own department's average — a correlated subquery classic.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

Outer query iterates each employee e.
(SELECT AVG(e2.salary) … WHERE e2.department_id = e.department_id) — a correlated subquery computing that employee's department average.
WHERE e.salary > (…) — keeps only above-department-average earners.

Correlated = re-run per row

The subquery executes for every outer row, so ensure the correlation column is indexed in production for performance.

Variations

Find movies budget above the genre average:

Movies (Movies/Actors/Ratings)

Loading database engine...

Common Mistakes

Subquery returns multiple rows. salary > (SELECT …) requires a single value; use > ALL/> ANY or an aggregate if multiple rows are expected.
Non-correlated by mistake. Forgetting e2.department_id = e.department_id compares against the global average, not the department's.
NULL in subquery with = / >. If the subquery is ever empty/NULL, the comparison is unknown and the row is dropped — handle edge cases deliberately.