Subqueries in the SELECT Clause
What you'll learn: How to embed subqueries directly in your SELECT statement to calculate scalar values for each row, enabling row-by-row dynamic calculations.
What Are SELECT Clause Subqueries?
A subquery in the SELECT clause acts like a custom calculator for each row in your result set. Unlike WHERE clause subqueries that filter data, these subqueries compute a single value that becomes an additional column in your output.
Think of it like adding a real-time lookup to each row: "For this specific customer, what's their average order value?" — and that calculation happens individually for every row returned.
How They Work
Each SELECT subquery:
- Must return exactly one value (scalar)
- Runs once per row in the outer query
- Can reference columns from the outer query (correlated)
- Appears as a column in your result set
Basic Pattern
SELECT
customer_name,
total_orders,
(SELECT AVG(order_amount)
FROM orders o
WHERE o.customer_id = c.customer_id) AS avg_order_value
FROM customers c;
Here, for each customer row, the subquery calculates their personal average order amount by filtering orders matching that customer's ID.
Common Use Cases
- Row-level aggregations: Comparing individual values to group statistics
- Lookups: Fetching related data without a JOIN
- Calculations: Computing percentages or ratios using aggregates
- Conditional values: Different logic per row based on related data
Key Takeaway: SELECT clause subqueries transform your result set by adding dynamically calculated columns, executing once per row to return scalar values that can reference the outer query's current row.