Subquery in SELECT

The Pattern: a Computed Column

Placing a scalar subquery (17.1) directly in the SELECT list turns it into a computed, per-row column. Unlike an uncorrelated scalar subquery (same value repeated for every row), a correlated scalar subquery in SELECT can compute something genuinely different for each outer row — that's the more interesting and more common real-world use.

Sample data used throughout this chapter:

employees

departments

Example: Department Headcount Per Employee

sql
SELECT e.name, e.department, e.salary,
       (SELECT COUNT(*) FROM employees e2 WHERE e2.department = e.department) AS dept_headcount
FROM employees e;

Result:

Note the inner table is aliased e2 specifically so it doesn't collide with the outer alias e — the correlation e2.department = e.department needs both names distinguishable.

Why This Executes Once Per Outer Row

Because the inner COUNT(*) depends on e.department, which changes for every outer row, the engine conceptually re-evaluates the subquery for each of the 8 rows returned. This is the SELECT-list version of the same "once per outer row" cost model discussed for correlated WHERE subqueries in 17.4 — and it's a real performance consideration: if employees had 5 million rows instead of 8, this naive per-row COUNT(*) could mean 5 million small aggregate scans unless the optimizer rewrites it (e.g., into a window function or a pre-aggregated join).

A Better Alternative for Large Tables

The same headcount result can be produced without any correlated subquery, using a window function:

sql
SELECT name, department, salary,
       COUNT(*) OVER (PARTITION BY department) AS dept_headcount
FROM employees;

This is typically far more efficient at scale because the engine computes all the per-partition counts in a single pass, rather than one subquery execution per row.

Edge Cases

• A SELECT-list subquery must be scalar (17.1) — one row, one column — or it's a runtime error the moment more than one row comes back for some outer row.
• If the correlated condition matches zero rows for a given outer row, the subquery returns NULL for that row specifically — it doesn't kill the whole query.
• Multiple subqueries can appear in the same SELECT list, each independently correlated or not.

Key Takeaways / Q&A

Q: Must a subquery in SELECT be scalar? A: Yes — always one row, one column, per outer row it's evaluated against.

Q: Is a SELECT-list subquery always correlated? A: No — it can be uncorrelated (17.1's company-average example) or correlated (this topic's headcount example); only the correlated form varies per row.

Q: What's the main performance risk? A: On large tables, a correlated scalar subquery in SELECT can mean the inner query effectively runs once per output row — window functions or a pre-aggregated JOIN are usually faster alternatives.