Course contentsShow
← Data Science
Lesson 967 of 2,14525. SQL Subqueries and CTEsFree lesson

Subqueries in the SELECT Clause

Learn how to use subqueries in SELECT to compute scalar values for each row in the result set.

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.