Correlated vs Non-Correlated Subqueries
Understand correlated subqueries that reference the outer row, their performance characteristics, and when EXISTS beats IN.
Correlated vs Non-Correlated Subqueries is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Non-Correlated Subqueries
A non-correlated subquery doesn't reference the outer row. It runs once and its result is reused:
SELECT * FROM users
WHERE id IN (
SELECT user_id FROM orders WHERE status = 'paid'
);Correlated Subqueries
A correlated subquery references the outer row — it logically re-runs for every outer row:
SELECT u.id, u.email
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id
AND o.total > 1000
);How to Tell the Difference
If the inner SELECT references a column from the outer FROM, it's correlated.
Performance Implications
A naïve correlated subquery is O(outer × inner). Modern planners often rewrite it into a semi-join or hash join — but you should still write the simpler form when possible.
EXISTS vs IN
For "at least one match":
-- IN with non-correlated subquery (often the planner's favourite):
SELECT u.* FROM users u
WHERE u.id IN (SELECT user_id FROM orders);
-- EXISTS with correlated subquery (NULL-safe alternative):
SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);NOT EXISTS Always Beats NOT IN
NOT IN breaks when the inner set contains NULL. NOT EXISTS doesn't. Always prefer NOT EXISTS for the anti-join:
-- BUG-prone:
SELECT u.* FROM users u
WHERE u.id NOT IN (SELECT excluded_id FROM blocklist);
-- Safe:
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM blocklist b WHERE b.excluded_id = u.id);Correlated Subqueries in SELECT
The "lookup per row" pattern:
SELECT u.id, u.email,
(SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id) AS last_order_at
FROM users u;
-- Often readable, but consider a LEFT JOIN + GROUP BY.Correlated Subqueries in UPDATE
Pull a derived value per row:
UPDATE products p
SET review_count = (
SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.id
);LATERAL JOIN: A Cleaner Correlated Pattern
For "look up some rows per outer row", LATERAL is often clearer and faster — covered in Advanced JOIN Patterns.
Subquery Caching
PostgreSQL doesn't cache subquery results across rows by default. If the same inner result is needed many times, use a CTE.
Index the Correlation Column
The inner subquery filters on the outer row's key. Without an index on that key, you get a full scan per outer row.
-- For the orders example, this is essential:
CREATE INDEX orders_user_id_idx ON orders(user_id);Recap
Correlated subqueries refer to the outer row.
- EXISTS is the canonical correlated form
- Prefer NOT EXISTS over NOT IN
- Index the correlation column
- LATERAL is a cleaner alternative for many cases
Quick Check
Why is NOT EXISTS safer than NOT IN when the inner set may contain NULL?
Frequently asked questions
Is the “Correlated vs Non-Correlated Subqueries” lesson free?
Yes — the full text of “Correlated vs Non-Correlated Subqueries” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Correlated vs Non-Correlated Subqueries”?
Understand correlated subqueries that reference the outer row, their performance characteristics, and when EXISTS beats IN. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Correlated vs Non-Correlated Subqueries” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Scalar, Row, and Table Subqueries
- Correlated vs Non-Correlated Subqueries
- Common Table Expressions (WITH)
- Recursive CTEs for Hierarchies