Correlated Subqueries

A correlated subquery refers to an outer column, so its answer changes per row. Which products cost more than the average of their own category?

Comparing each product with its own category's averageSQL

SELECT p.sku, p.category_id, p.price FROM products AS p
WHERE p.price > (SELECT AVG(q.price) FROM products AS q
                 WHERE q.category_id = p.category_id);

Output

+-----------+-------------+-------+
| sku       | category_id | price |
+-----------+-------------+-------+
| BK-SQL-01 |           3 | 44.50 |
| BK-LAR-01 |           2 | 49.00 |
| AC-MUG-01 |           5 | 12.50 |
+-----------+-------------+-------+
3 rows in set (0.001 sec)

The mug beats its category's 8.745. Alias both tables: a bare category_id inside would mean q's. Logically the subquery reruns per outer row; EXPLAIN FORMAT=TREE marks it "dependent", probing the index on q.category_id. Most EXISTS tests are correlated too.

Without an index on the correlation column, each outer row rescans the inner table. Index it, or build the inner result once: join a grouped derived table (Derived Tables and LATERAL), or use AVG(price) OVER (PARTITION BY category_id) (OVER and PARTITION BY). The subquery_to_derived optimizer switch, off by default, makes the first rewrite automatically.

MENU

Correlated Subqueries