SQL 45 🛢️ Correlated Subqueries
A subquery that references a column from the outer query is correlated. This single characteristic changes everything about how the subquery behaves. An uncorrelated subquery runs once and produces a result that the outer query uses. A correlated subquery runs once for every row the outer query processes, because its result depends on the specific row being evaluated. This makes correlated subqueries powerful—they can compute per-row values that no uncorrelated subquery can—and potentially expensive, because the work multiplies with the size of the outer table.
This chapter covers correlated subqueries in depth. You will learn what makes a subquery correlated, how the database executes it, and why the execution model matters for performance. You will see the three main patterns where correlated subqueries appear: EXISTS and NOT EXISTS for existence checks, scalar subqueries in SELECT for per-row lookups, and comparisons against aggregates computed per group. You will also learn how to recognize when a correlated subquery should be rewritten as a join or a window function.
By the end, you will understand not just the syntax but the mental model: a correlated subquery is a query that depends on the current row, and that dependency is both its power and its cost.
Key point: A correlated subquery cannot run independently of the outer query. It references at least one column from the outer query, which means the database must evaluate it once per outer row. This is the defining characteristic and the defining performance consideration.
Why correlated subqueries exist
The per-row dependency problem. Some questions can only be answered with reference to the current row. “Find employees who earn more than the average salary in their department” requires computing a different average for each employee. “Find customers whose most recent order exceeds $500” requires looking at a different set of orders for each customer. Uncorrelated subqueries cannot express these because they produce a single result for the entire query. Correlated subqueries express the dependency directly: the subquery’s result depends on the outer row being evaluated.
The existence check problem. EXISTS and NOT EXISTS are the most common forms of correlated subqueries. They ask whether any row in another table matches a condition involving the outer row. “Find customers who have placed an order” is a correlated existence check: for each customer, does any order exist with that customer’s ID? This pattern is so common that EXISTS is often the default mental model for correlated subqueries.
The join alternative. Every correlated subquery can be rewritten as a join. The question is which form is clearer and which performs better. A correlated EXISTS check often performs better than a join because it can short-circuit as soon as a match is found, while a join must produce all matching rows. A correlated scalar subquery in SELECT is often equivalent to a LEFT JOIN with an aggregate, though the optimizer may handle them differently.
The performance tension. Correlated subqueries are conceptually elegant but potentially slow. If the outer table has a million rows and the subquery scans a large table each time, the query runs a million scans. Indexes on the correlation column are essential. Modern optimizers can sometimes decorrelate a subquery—rewrite it as a join—which eliminates the per-row execution. But this is not guaranteed, and understanding the execution model helps you write queries that perform well regardless.
The window function alternative. Many correlated subqueries that compute per-group aggregates can be rewritten using window functions. “Find employees earning more than the department average” becomes a window function with AVG() OVER (PARTITION BY department). Window functions are often faster because they compute all group aggregates in a single pass. Recognizing when a window function can replace a correlated subquery is a key skill.
a. EXISTS and NOT EXISTS correlated subqueries
The most common correlated subquery is the EXISTS check. It runs for each outer row and returns true if the subquery finds at least one matching row.
-- Find customers who have placed at least one order
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
);The subquery references c.customer_id from the outer query. For each customer, the database runs the subquery to check whether any order exists for that customer. The SELECT 1 is conventional; the columns selected do not matter because EXISTS only checks for row presence.
The NOT EXISTS form finds rows with no matching subquery result.
-- Find customers who have never placed an order
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
);NOT EXISTS is the null-safe alternative to NOT IN. If the subquery’s comparison column contains NULL, NOT EXISTS still behaves correctly, while NOT IN returns no rows.
The short-circuit behavior of EXISTS is important for performance. As soon as the subquery finds one matching row, it stops scanning. This makes EXISTS efficient even on large tables, provided the correlation column is indexed.
b. Scalar correlated subqueries in SELECT
A scalar subquery in the SELECT list that references the outer query is also correlated. It computes a value per outer row.
-- For each customer, show their total order value
SELECT
c.customer_id,
c.name,
(SELECT SUM(o.amount)
FROM orders o
WHERE o.customer_id = c.customer_id) AS total_spent
FROM customers c;For each customer, the subquery computes the sum of their orders. The result appears as a column in the output. If a customer has no orders, the subquery returns NULL.
This pattern is equivalent to a LEFT JOIN with GROUP BY:
SELECT c.customer_id, c.name, SUM(o.amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;Both produce the same result. The correlated subquery form is often clearer when the aggregate is simple and only one column is needed. The join form is often faster because it processes both tables together rather than running the subquery per row.
A practical rule: if the correlated subquery in SELECT returns an aggregate from a single table, consider whether a LEFT JOIN with GROUP BY would be cleaner and faster. Modern optimizers often treat them equivalently, but the join form is more explicit about the intent.
c. Correlated subqueries with aggregates per group
A third pattern uses correlated subqueries to compare each row against an aggregate computed for its group.
-- Find employees earning more than their department average
SELECT e.employee_id, e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department = e.department
);The subquery computes the average salary for the employee’s department. The outer query filters employees whose salary exceeds that average. The subquery is correlated on e.department.
This pattern is useful but can be slow on large tables because the subquery runs for each employee. A window function alternative is often faster:
SELECT employee_id, name, department, salary
FROM (
SELECT
employee_id,
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
) AS ranked
WHERE salary > dept_avg;The window function computes the average for all departments in a single pass. The derived table allows filtering on the computed average. This form is typically more efficient than the correlated subquery for large tables.
The choice between the two depends on the database’s optimizer and the specific query. Some optimizers decorrelate the subquery automatically, producing the same execution plan. Others do not, and the window function form is significantly faster. Testing both forms on representative data is the only way to know for sure.
Complete Example Session
-- ============================================
-- PART 1: SAMPLE DATA
-- ============================================
CREATE TABLE employees (
employee_id INT,
name VARCHAR(50),
department VARCHAR(20),
salary DECIMAL(10,2)
);
INSERT INTO employees VALUES
(1, 'Alice', 'Engineering', 95000),
(2, 'Bob', 'Engineering', 85000),
(3, 'Carol', 'Sales', 75000),
(4, 'Dave', 'Sales', 65000),
(5, 'Eve', 'Marketing', 70000),
(6, 'Frank', 'Marketing', 72000);-- ============================================
-- PART 2: EXISTS CORRELATED SUBQUERY
-- ============================================
SELECT employee_id, name
FROM employees e
WHERE EXISTS (
SELECT 1 FROM employees e2
WHERE e2.department = e.department
AND e2.salary > e.salary
);
-- Employees who are not the highest paid in their department-- ============================================
-- PART 3: NOT EXISTS CORRELATED SUBQUERY
-- ============================================
SELECT employee_id, name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM employees e2
WHERE e2.department = e.department
AND e2.salary > e.salary
);
-- Employees who ARE the highest paid in their department-- ============================================
-- PART 4: SCALAR CORRELATED SUBQUERY IN SELECT
-- ============================================
SELECT
employee_id,
name,
department,
salary,
(SELECT AVG(e2.salary) FROM employees e2
WHERE e2.department = e.department) AS dept_avg
FROM employees e;-- ============================================
-- PART 5: COMPARISON WITH DEPARTMENT AVERAGE
-- ============================================
SELECT employee_id, name, salary
FROM employees e
WHERE salary > (
SELECT AVG(e2.salary) FROM employees e2
WHERE e2.department = e.department
);-- ============================================
-- PART 6: SAME RESULT WITH WINDOW FUNCTION
-- ============================================
SELECT employee_id, name, salary
FROM (
SELECT
employee_id,
name,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
) AS ranked
WHERE salary > dept_avg;-- ============================================
-- PART 7: CORRELATED SUBQUERY WITH COUNT
-- ============================================
SELECT
department,
(SELECT COUNT(*) FROM employees e2
WHERE e2.department = e1.department) AS dept_count
FROM employees e1
GROUP BY department;-- ============================================
-- PART 8: CORRELATED SUBQUERY WITH MAX
-- ============================================
SELECT employee_id, name, salary
FROM employees e
WHERE salary = (
SELECT MAX(e2.salary) FROM employees e2
WHERE e2.department = e.department
);
-- Highest paid employee per department-- ============================================
-- PART 9: CORRELATED SUBQUERY WITH ROW CONSTRUCTOR
-- ============================================
SELECT employee_id, name, department, salary
FROM employees e
WHERE (department, salary) IN (
SELECT department, MAX(salary)
FROM employees
GROUP BY department
);
-- Same as above but with row constructor-- ============================================
-- PART 10: COMPARISON OF FORMS
-- ============================================
-- Correlated subquery form
SELECT employee_id, name FROM employees e
WHERE salary > (SELECT AVG(e2.salary) FROM employees e2
WHERE e2.department = e.department);
-- Window function form
SELECT employee_id, name FROM (
SELECT employee_id, name, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
) AS ranked WHERE salary > dept_avg;
-- Join form
SELECT e.employee_id, e.name
FROM employees e
JOIN (SELECT department, AVG(salary) AS dept_avg
FROM employees GROUP BY department) AS d
ON d.department = e.department
WHERE e.salary > d.dept_avg;The ten parts covered the essential patterns of correlated subqueries: EXISTS and NOT EXISTS, scalar subqueries in SELECT, comparisons against per-group aggregates, the window function alternative, correlated counts and max values, row constructors, and a direct comparison of three equivalent forms.
Quick Reference
Correlated vs Uncorrelated
| Aspect | Uncorrelated | Correlated |
|---|---|---|
| References outer query | No | Yes |
| Executes | Once | Once per outer row |
| Result | Same for all rows | Depends on current row |
| Performance | Usually faster | Potentially slower |
| Example | IN (SELECT ...) | EXISTS (SELECT ... WHERE x = outer.x) |
Correlated Subquery Patterns
| Pattern | Purpose | Example |
|---|---|---|
EXISTS | Check for matching rows | WHERE EXISTS (SELECT 1 ...) |
NOT EXISTS | Check for absence | WHERE NOT EXISTS (SELECT 1 ...) |
Scalar in SELECT | Per-row lookup | (SELECT MAX(...) ...) AS col |
| Aggregate comparison | Compare to group aggregate | WHERE x > (SELECT AVG(...) ...) |
Alternatives to Correlated Subqueries
| Correlated Form | Alternative | When Better |
|---|---|---|
EXISTS | INNER JOIN | When you need joined columns |
NOT EXISTS | LEFT JOIN ... IS NULL | When join is clearer |
Scalar in SELECT | LEFT JOIN + GROUP BY | When aggregating multiple columns |
| Aggregate comparison | Window function | When many rows per group |
Performance Considerations
| Factor | Impact |
|---|---|
| Index on correlation column | Essential for performance |
| Outer table size | Larger = more subquery executions |
Short-circuit in EXISTS | Stops at first match |
| Optimizer decorrelation | May eliminate per-row execution |
| Window function alternative | Single-pass computation |
Best Practices
✅ Do This:
-- Use EXISTS for existence checks
SELECT c.id FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.id); -- ✅
-- Use NOT EXISTS instead of NOT IN
SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.id); -- ✅
-- Index the correlation column
CREATE INDEX idx_orders_customer ON orders(customer_id); -- ✅
-- Use window functions for per-group aggregates
SELECT * FROM (
SELECT id, salary,
AVG(salary) OVER (PARTITION BY dept) AS avg
FROM employees
) AS t WHERE salary > avg; -- ✅
-- Use COALESCE for scalar subqueries that may return NULL
SELECT c.name,
COALESCE((SELECT SUM(o.amount) FROM orders o
WHERE o.customer_id = c.id), 0) AS total -- ✅
FROM customers c;❌ Don’t Do This:
-- Use NOT IN with nullable subquery
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders); -- ❌
-- Use scalar subquery in SELECT when join is clearer
SELECT c.name,
(SELECT SUM(o.amount) FROM orders o
WHERE o.customer_id = c.id) AS total, -- ⚠️
(SELECT COUNT(*) FROM orders o
WHERE o.customer_id = c.id) AS count -- ⚠️
FROM customers c; -- use LEFT JOIN instead
-- Correlate without an index
SELECT * FROM large_table t
WHERE EXISTS (SELECT 1 FROM other o
WHERE o.unindexed_col = t.id); -- ❌
-- Forget correlation column in subquery
SELECT c.id FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o); -- ❌ (always true)Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
EXISTS always true | Missing correlation | Add WHERE linking to outer |
| Slow query | No index on correlation column | Create index |
NOT IN returns nothing | Subquery contains NULL | Use NOT EXISTS |
| Scalar subquery error | Returns multiple rows | Use aggregate or IN |
| Repeated subquery cost | Multiple scalar subqueries in SELECT | Combine into one join |
| Wrong aggregate | Missing WHERE correlation | Ensure subquery scoped correctly |
Real-World Examples
1. Customers With Orders
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id);2. Customers Without Orders
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id);3. Last Order Date per Customer
SELECT c.name,
(SELECT MAX(o.order_date) FROM orders o
WHERE o.customer_id = c.customer_id) AS last_order
FROM customers c;4. Employees Above Department Average
SELECT e.name FROM employees e
WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2
WHERE e2.department = e.department);5. Highest Paid per Department
SELECT e.name, e.department FROM employees e
WHERE e.salary = (SELECT MAX(e2.salary) FROM employees e2
WHERE e2.department = e.department);6. Products Never Ordered
SELECT p.name FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi
WHERE oi.product_id = p.product_id);7. Users With Recent Login
SELECT u.name FROM users u
WHERE EXISTS (SELECT 1 FROM logins l
WHERE l.user_id = u.user_id
AND l.login_date > CURRENT_DATE - 30);8. Departments With No Employees
SELECT d.name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e
WHERE e.department_id = d.id);9. Order Count per Customer
SELECT c.name,
(SELECT COUNT(*) FROM orders o
WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;10. Correlated Subquery in HAVING
SELECT department, COUNT(*) AS emp_count
FROM employees e
GROUP BY department
HAVING COUNT(*) > (SELECT AVG(dept_count) FROM (
SELECT COUNT(*) AS dept_count FROM employees
GROUP BY department
) AS counts);Visual
Correlated vs Uncorrelated Execution
┌─────────────────────────────────────────────────────────────┐
│ UNCORRELATED: SUBQUERY RUNS ONCE │
│ │
│ SELECT id FROM customers │
│ WHERE id IN (SELECT customer_id FROM orders); │
│ │
│ Step 1: Run subquery once │
│ → [1, 3, 5, 7] │
│ │
│ Step 2: Scan customers, check membership │
│ For each customer: id IN [1, 3, 5, 7]? │
│ │
├─────────────────────────────────────────────────────────────┤
│ │
│ CORRELATED: SUBQUERY RUNS PER OUTER ROW │
│ │
│ SELECT id FROM customers c │
│ WHERE EXISTS (SELECT 1 FROM orders o │
│ WHERE o.customer_id = c.id); │
│ │
│ For each customer row: │
│ Step 1: Read c.id │
│ Step 2: Run subquery with that id │
│ Step 3: Check if any row returned │
│ │
│ N customers = N subquery executions. │
│ │
└─────────────────────────────────────────────────────────────┘EXISTS Short-Circuit Behavior
┌─────────────────────────────────────────────────────────────┐
│ EXISTS STOPS AT FIRST MATCH │
│ │
│ WHERE EXISTS (SELECT 1 FROM orders o │
│ WHERE o.customer_id = c.id) │
│ │
│ For customer 1 with 100 orders: │
│ Scan orders for customer_id = 1 │
│ First match found → STOP │
│ Does not scan remaining 99 orders │
│ │
│ This is why EXISTS is efficient even on large tables. │
│ Index on o.customer_id makes the first match immediate. │
│ │
└─────────────────────────────────────────────────────────────┘Correlated Subquery vs Window Function
┌─────────────────────────────────────────────────────────────┐
│ CORRELATED SUBQUERY │
│ │
│ SELECT e.name FROM employees e │
│ WHERE e.salary > ( │
│ SELECT AVG(e2.salary) FROM employees e2 │
│ WHERE e2.department = e.department │
│ ); │
│ │
│ For each employee: run subquery for their department │
│ → N employees = N subquery executions │
│ │
├─────────────────────────────────────────────────────────────┤
│ │
│ WINDOW FUNCTION │
│ │
│ SELECT name FROM ( │
│ SELECT name, salary, │
│ AVG(salary) OVER (PARTITION BY department) AS avg │
│ FROM employees │
│ ) AS t WHERE salary > avg; │
│ │
│ Single pass: compute average for all departments │
│ → One scan of the table │
│ │
│ Window function is usually faster for this pattern. │
│ │
└─────────────────────────────────────────────────────────────┘Correlation Column Index Impact
┌─────────────────────────────────────────────────────────────┐
│ WITHOUT INDEX │
│ │
│ For each outer row: │
│ Full scan of inner table to find matches │
│ │
│ Outer rows = 10,000 │
│ Inner table = 1,000,000 rows │
│ Total work = 10,000 × 1,000,000 = 10^10 row reads │
│ │
├─────────────────────────────────────────────────────────────┤
│ │
│ WITH INDEX ON CORRELATION COLUMN │
│ │
│ For each outer row: │
│ Index lookup finds matches directly │
│ │
│ Outer rows = 10,000 │
│ Index lookup = O(log n) ≈ 20 operations │
│ Total work = 10,000 × 20 = 200,000 operations │
│ │
│ Orders of magnitude faster. │
│ │
└─────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Definition | Subquery that references the outer query |
| Execution | Once per outer row |
| Common forms | EXISTS, NOT EXISTS, scalar in SELECT |
| Performance factor | Index on correlation column |
EXISTS behavior | Short-circuits at first match |
NOT EXISTS | Null-safe alternative to NOT IN |
Scalar in SELECT | Runs per row, returns NULL if no match |
| Aggregate comparison | Compare row to group aggregate |
| Window function alternative | Single-pass, often faster |
| Join alternative | Often equivalent, optimizer-dependent |
Key takeaways:
- Correlated subqueries reference the outer query. This dependency means the subquery cannot run independently. It runs once for each row the outer query processes.
EXISTSandNOT EXISTSare the most common forms. They check for the presence or absence of matching rows.EXISTSshort-circuits as soon as a match is found, making it efficient even on large tables.NOT EXISTSis null-safe;NOT INis not. If the subquery’s comparison column containsNULL,NOT INreturns no rows.NOT EXISTShandles nulls correctly.- Scalar correlated subqueries in
SELECTcompute per-row values. They are useful for lookups like “most recent order date” or “total spent.” They returnNULLwhen no matching rows exist. - Indexes on the correlation column are essential. Without an index, the subquery scans the inner table for each outer row. With an index, it finds matches directly.
- Window functions are often faster alternatives. For per-group aggregates, a window function computes all groups in a single pass, while a correlated subquery may run per row.
- Modern optimizers may decorrelate subqueries. Some databases automatically rewrite correlated subqueries as joins. This is not guaranteed, so test performance on representative data.
- Every correlated subquery has a join equivalent. The choice between them is a matter of readability and performance. Test both forms when performance matters.
Remember: Correlated subqueries are powerful because they express per-row dependencies directly. They are dangerous because that per-row execution can be slow. The key to using them well is understanding the execution model: the subquery runs once per outer row, and the cost multiplies with the outer table size. Index the correlation column. Consider whether EXISTS, a join, or a window function is the better fit. Write the form that expresses the logic most clearly, then measure, then optimize if needed.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!