SQL Correlated Subqueries: How They Work, With Examples

Start with a normal subquery. This compares every employee with the company-wide average salary. The subquery has no link to the outer query, so it is worked out once:

SELECT COUNT(*) AS above_company_average
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

▶ Run this query in the SQL Playground

Now compare each employee with the average of their own department. The subquery needs to know which department the current employee is in, so it refers to the outer row through the alias e:

SELECT e.first_name, e.last_name, e.department, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(x.salary)
                  FROM employees x
                  WHERE x.department = e.department)
ORDER BY e.department, e.salary DESC
LIMIT 8;

▶ Run this query in the SQL Playground

x.department = e.department is the correlation. It makes the subquery a different question for each employee: an Engineering employee is compared with the Engineering average, a Sales employee with the Sales average. 59 employees beat their department average, against 55 who beat the company average.

SELECT COUNT(*) AS above_department_average
FROM employees e
WHERE e.salary > (SELECT AVG(x.salary)
                  FROM employees x
                  WHERE x.department = e.department);

▶ Run this query in the SQL Playground

SELECT ...
FROM outer_table o
WHERE o.column  operator  (SELECT aggregate(i.column)
                           FROM inner_table i
                           WHERE i.key = o.key);   -- refers to the outer row

Conceptually, the database processes it like this:

Because the subquery refers to e, it cannot be run on its own: run it separately and the database reports that e.department does not exist. In practice optimizers often rewrite a correlated subquery into a join, so "once per row" describes the meaning, not necessarily the work done.

The highest-paid employee in each department

SELECT e.department, e.first_name, e.last_name, e.salary
FROM employees e
WHERE e.salary = (SELECT MAX(x.salary)
                  FROM employees x
                  WHERE x.department = e.department)
ORDER BY e.salary DESC;

▶ Run this query in the SQL Playground

Products priced above their category average

SELECT p.category, p.name, p.price
FROM products p
WHERE p.price > (SELECT AVG(q.price)
                 FROM products q
                 WHERE q.category = p.category)
ORDER BY p.category, p.price DESC
LIMIT 8;

▶ Run this query in the SQL Playground

Each customer's most recent order

SELECT o.user_id, o.id AS order_id, o.order_date, o.total
FROM orders o
WHERE o.order_date = (SELECT MAX(x.order_date)
                      FROM orders x
                      WHERE x.user_id = o.user_id)
ORDER BY o.user_id
LIMIT 8;

▶ Run this query in the SQL Playground

If a customer had two orders on their latest date, both would appear. Add a tie-breaker, such as comparing id instead of the date, when you need exactly one row per customer.

A correlated subquery that returns one value can be a column of its own. This shows each customer with their number of orders and their largest order:

SELECT u.id, u.first_name, u.last_name,
       (SELECT COUNT(*)   FROM orders o WHERE o.user_id = u.id) AS orders,
       (SELECT MAX(total) FROM orders o WHERE o.user_id = u.id) AS largest_order
FROM users u
ORDER BY u.id
LIMIT 6;

▶ Run this query in the SQL Playground

For a customer with no orders, COUNT returns 0 and MAX returns NULL. A subquery in SELECT must return at most one row; if it can return more, the query fails in most databases (SQLite silently uses the first row).

EXISTS is almost always used with a correlated subquery. It keeps the rows for which the subquery finds at least one match:

SELECT COUNT(*) AS reviewed_products
FROM products p
WHERE EXISTS (SELECT 1
              FROM reviews r
              WHERE r.product_id = p.id);

▶ Run this query in the SQL Playground

NOT EXISTS finds the rows with no match, here the customers who have never ordered:

SELECT u.id, u.first_name, u.last_name
FROM users u
WHERE NOT EXISTS (SELECT 1
                  FROM orders o
                  WHERE o.user_id = u.id)
ORDER BY u.id
LIMIT 6;

▶ Run this query in the SQL Playground

NOT EXISTS is the safe way to ask "has no match"; NOT IN returns no rows at all if its list contains a NULL. See IN vs EXISTS.

The "above department average" question can be written three ways. All three return the same 59 employees.

JOIN to a grouped result

SELECT COUNT(*) AS above_department_average
FROM employees e
JOIN (SELECT department, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department) d ON d.department = e.department
WHERE e.salary > d.avg_salary;

▶ Run this query in the SQL Playground

Window function

SELECT COUNT(*) AS above_department_average
FROM (SELECT salary,
             AVG(salary) OVER (PARTITION BY department) AS dept_avg
      FROM employees) t
WHERE salary > dept_avg;

▶ Run this query in the SQL Playground

For existence checks, EXISTS is usually the clearest. For "compare with my group's value", window functions are often the fastest on large tables. Check the plans with EXPLAIN when it matters.

Five errors and how to avoid them.

SELECT *
FROM employees e
WHERE salary > (SELECT AVG(salary)
                FROM employees);

SELECT *
FROM employees e
WHERE salary > (SELECT AVG(x.salary)
                FROM employees x
                WHERE x.department = e.department);

SELECT *
FROM employees e
WHERE salary > (SELECT AVG(salary)
                FROM employees e
                WHERE e.department = e.department);

SELECT *
FROM employees e
WHERE salary > (SELECT AVG(x.salary)
                FROM employees x
                WHERE x.department = e.department);

SELECT u.first_name,
       (SELECT total FROM orders o
        WHERE o.user_id = u.id) AS order_total
FROM users u;

SELECT u.first_name,
       (SELECT SUM(total) FROM orders o
        WHERE o.user_id = u.id) AS order_total
FROM users u;

WHERE id NOT IN (SELECT manager_id FROM employees)

WHERE NOT EXISTS (SELECT 1 FROM employees r
                  WHERE r.manager_id = e.id)

-- orders has millions of rows and no index on user_id
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)

CREATE INDEX idx_orders_user_id ON orders(user_id);

6 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Count the orders that are larger than the same customer's average order. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS above_own_average
   FROM orders o
   WHERE o.total > (SELECT AVG(x.total)
                    FROM orders x
                    WHERE x.user_id = o.user_id);

   ▶ Run this query in the SQL Playground

   The subquery averages only the orders of the customer who placed the current order.

2. 2Easy Show the first 6 customers with the number of reviews each has written. Table: users, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT u.id, u.first_name,
          (SELECT COUNT(*) FROM reviews r WHERE r.user_id = u.id) AS reviews
   FROM users u
   ORDER BY u.id
   LIMIT 6;

   ▶ Run this query in the SQL Playground

   A correlated subquery in SELECT gives one value per customer, 0 when there is none.

3. 3Medium List the most recently hired employee in each department. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT e.department, e.first_name, e.last_name, e.hire_date
   FROM employees e
   WHERE e.hire_date = (SELECT MAX(x.hire_date)
                        FROM employees x
                        WHERE x.department = e.department)
   ORDER BY e.hire_date DESC;

   ▶ Run this query in the SQL Playground

   MAX over the employee's own department, compared with their hire date.

4. 4Medium Find the products whose average review rating is below 2.5. Show 8, lowest first. Table: products, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT p.name,
          (SELECT ROUND(AVG(r.rating), 2) FROM reviews r WHERE r.product_id = p.id) AS avg_rating
   FROM products p
   WHERE (SELECT AVG(r.rating) FROM reviews r WHERE r.product_id = p.id) < 2.5
   ORDER BY avg_rating, p.name
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   Products without reviews have a NULL average, and NULL < 2.5 is not true, so they drop out.

5. 5Medium Show each department with the number of employees who earn more than 150,000 in it. Table: departments, employees

   Show solution

   SQLite · runs in the Playground

   SELECT d.name,
          (SELECT COUNT(*) FROM employees e
           WHERE e.department_id = d.id AND e.salary > 150000) AS high_earners
   FROM departments d
   ORDER BY high_earners DESC, d.name;

   ▶ Run this query in the SQL Playground

   The correlation uses department_id, the key that links the two tables.

6. 6Hard Rewrite exercise 1 with a window function and confirm the count is the same. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS above_own_average
   FROM (SELECT total,
                AVG(total) OVER (PARTITION BY user_id) AS user_avg
         FROM orders) t
   WHERE total > user_avg;

   ▶ Run this query in the SQL Playground

   AVG ... OVER (PARTITION BY user_id) puts each customer's average on every one of their orders in a single pass.