Subquery in SELECT
intermediatesubqueries
2 min read
What is a Subquery in SELECT?
A subquery in the SELECT clause computes a value for each row of the outer query. It must return exactly one value (one row, one column) — this is called a scalar subquery.
Syntax
SELECT
column1,
(SELECT aggregate(col) FROM table2 WHERE table2.fk = table1.id) AS computed_value
FROM table1;When to Use
- Adding a count of related rows to each row
- Including an aggregated value alongside detail rows
- Computing a per-row metric from another table
Key Points
- Scalar Only — The subquery must return exactly one value. Multiple rows or columns cause an error.
- Correlated — Usually correlated, meaning it references the outer query's columns.
- Per-Row Execution — Conceptually runs once per outer row (though the optimizer may optimize this).
- NULL on No Match — If the subquery returns no rows, the result is NULL.
- Alternative — LEFT JOIN with an aggregate or a window function may be more efficient.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Subquery in SELECT Challenge
Write a query that solve this task: show each student alongside their enrollment count.
Expected result
Each student with a course_count column showing how many courses they are enrolled in.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is a Subquery in SELECT?
Initializing database...Each run starts from fresh sample data.
More Examples
Latest order date per product
Show each product with the date of its most recent order.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What happens if the subquery returns more than one row?
PostgreSQL raises an error: 'more than one row returned by a subquery used as an expression'. Ensure the subquery returns exactly one value.
Are SELECT subqueries slow?
They can be if the outer query has many rows, since the subquery conceptually runs per row. The optimizer often converts them to joins, but for large datasets consider using an explicit JOIN.