Correlated subqueries
A correlated subquery refers to the outer query's row and runs once per row.
In this page:
Syntax
SELECT columns
FROM table_name outer_alias
WHERE column > (
SELECT AVG(column) FROM table_name WHERE group_column = outer_alias.group_column
);Correlated subqueries
The inner query uses a column from the outer query, such as comparing each employee's salary to the average of their own department. They are expressive but can be slow on big data, and are often faster as a join or window function.
The planner sometimes turns them into joins automatically.
Note:
Consider a window function or join before using a correlated subquery.
Example: Correlated subqueries
CREATE TABLE emp (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO emp VALUES (1,'Ada','eng',100),(2,'Bob','eng',80),(3,'Cy','ops',60),(4,'Di','ops',70);
SELECT e.name, e.dept, e.salary FROM emp e WHERE e.salary > (SELECT AVG(salary) FROM emp WHERE dept = e.dept) ORDER BY e.id;
SELECT name, (SELECT COUNT(*) FROM emp x WHERE x.dept = e.dept) AS dept_size FROM emp e ORDER BY id;
-- Output:
-- name | dept | salary
-- Ada | eng | 100
-- Di | ops | 70
-- name | dept_size
-- Ada | 2
-- Bob | 2
-- Cy | 2
-- Di | 2Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Assuming correlated subqueries run once
- Correlating on unindexed columns
- Forgetting the alias to reference the outer row
Chapter Summary
- Refers to columns of the outer query
- Evaluated per outer row
- Can be slow on large tables
- Joins and windows may be faster
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: