A correlated subquery references a value from an enclosing query, making its logical result depend on the current outer row.
A customer-specific total can be calculated by a subquery that refers to that customer’s id. The correlation makes the relationship visible. Understand its scope and result cardinality before assuming it behaves like a general independent subquery.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
MySQL, PostgreSQL, SQL Server and SQLite
Correlated scalar aggregates and EXISTS are supported by all four systems. A scalar subquery that returns multiple rows is rejected by PostgreSQL, MySQL and SQL Server; SQLite’s behaviour differs, so do not depend on it. Optimisation of correlated forms varies by engine and query shape.
Check the engine-specific syntax and version requirements in PostgreSQL scalar subqueries.
Practice task and worked solution
Return each customer with a correlated count of all matching sale records, including a zero for Dev.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
SELECT c.id,c.name,(
SELECT COUNT(*) FROM sales AS s WHERE s.customer_id=c.id
) AS sale_count FROM customers AS c ORDER BY c.id;id | name | sale_count
1 | Ana | 2
2 | Ben | 1
3 | Cara | 2
4 | Dev | 0COUNT returns zero over an empty input, unlike SUM’s NULL. Cara’s unknown amount still belongs to a real matching sale and is counted. The outer customer population remains four rows because each scalar aggregate returns exactly one summary value.
Remove the c.id correlation and predict eighty-five beside every customer, then restore it to recover customer-specific totals. Change SUM to COUNT(*) and observe Dev’s zero instead of NULL. Finally attempt a raw amount scalar lookup for Ana in a controlled test: her two sales demonstrate why an unaggregated many-row result is not a portable scalar expression.
SQL Correlated Subqueries: Outer Rows and Scalar Results Syntax
Correlated subqueries
SELECT c.id,c.name,(
SELECT SUM(s.amount) FROM sales AS s WHERE s.customer_id=c.id
) AS known_total FROM customers AS c ORDER BY c.id;SQL Correlated Subqueries: Outer Rows and Scalar Results Summary
- A correlated subquery refers to a column of the outer query, so its result depends on the current outer row.
- A scalar subquery in
SELECTmust return at most one value per row, usually through an aggregate. - An aggregate over no matching rows gives
NULL; wrap it inCOALESCEif "none" should show as 0. - Without the correlation condition, the subquery returns the same answer for every row.
- For several per-row measures, a pre-aggregated join is often clearer and faster.
SQL Correlated Subqueries: Outer Rows and Scalar Results Examples
Each example shows the code, the output it produces and what each part does.
Each customer's known total
Each customer id defines the inner sales selection. Ana’s known sum is forty, Ben’s is twenty and Cara’s is twenty despite one missing amount. Dev has no matching input and receives the empty-sum NULL. The anonymous sale matches no customer.
SELECT c.id,c.name,(
SELECT SUM(s.amount) FROM sales AS s WHERE s.customer_id=c.id
) AS known_total FROM customers AS c ORDER BY c.id;id | name | known_total
1 | Ana | 40
2 | Ben | 20
3 | Cara | 20
4 | Dev | NULLCommon SQL Correlated Subqueries: Outer Rows and Scalar Results Mistakes
Losing the correlation condition
Removing s.customer_id=c.id makes the inner SUM independent of the outer customer. Every customer then receives the full known total eighty-five. The query still runs and can look plausible, so compare a customer with multiple sales and a customer with none to expose missing correlation.
SQL Correlated Subqueries: Outer Rows and Scalar Results in Real Projects
Per-customer measures in an export
A customer export can display several per-customer summaries while preserving the customer row. A scalar subquery is readable for a small isolated measure; a preaggregated join may be clearer for many related metrics. Choose a shape that keeps one customer row and makes missing-history policy explicit.
A scalar measure should have a clear one-value guarantee, such as an aggregate without GROUP BY or a lookup on a genuine unique key. Preserve customer population and missing-history policy when comparing a correlated version with a grouped-join rewrite. Check the execution plan for costly repetitions without assuming every correlated expression is inherently slow.
SQL Correlated Subqueries: Outer Rows and Scalar Results FAQ
Does correlation always mean poor performance?
No. Conceptually the subquery runs once per outer row, but optimisers often rewrite it as a join, and an index on the correlated column, such as sales.customer_id, makes each lookup fast. It depends on indexes, cardinality and the optimiser's transformations.
Can a scalar subquery return many rows?
Do not rely on that: PostgreSQL, MySQL and SQL Server raise an error, while SQLite silently uses the first row. Define a one-value result through aggregation, a valid unique key or a deliberate row selection with ORDER BY ... LIMIT 1.
How can I recognise correlation?
Look for an inner expression qualified by an alias defined in the outer query, such as c.id inside a subquery that reads sales AS s. If the subquery could run on its own, it isn't correlated.
Can a correlated subquery appear in WHERE?
Yes. WHERE s.amount > (SELECT AVG(s2.amount) FROM sales s2 WHERE s2.customer_id = s.customer_id) keeps sales larger than that customer's own average. Give the inner and outer references to the same table different aliases.
Should I rewrite a correlated subquery as a join?
When you need several values per row, yes: aggregate the sales once per customer in a derived table or CTE and join it, rather than repeating three correlated subqueries. For a single value or an EXISTS check, the correlated form is often the clearest.
SQL Correlated Subqueries: Outer Rows and Scalar Results Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
What correlated known_total is reported for Dev?
SQL Correlated Subqueries: Outer Rows and Scalar Results Quiz
2 questions to check what you learned.
Which reference makes the subquery correlated?
- Parentheses alone, even without any enclosing-row reference
- The inner predicate’s reference to c.id from the outer query
- The selected alias known_total by itself
Why does correlated COUNT give Dev zero?
- COUNT treats the left-side customer itself as one matching sale
- COUNT converts the empty input into one zero-valued sale record
- COUNT over his empty matching input returns zero