Eric Campbell 5 min read Engineering

SQL - Subquery in SELECT Clause

A subquery in the SELECT clause is a query nested inside the column list of your main query. Unlike subqueries in WHERE or FROM clauses, these must return exactly one value—a single row with a single...

Key Insights

  • Subqueries in the SELECT clause must return exactly one value (scalar subqueries), making them ideal for per-row calculations like fetching related aggregates or single lookup values.
  • Correlated subqueries execute once per row in the outer query, which can devastate performance on large datasets—always consider rewriting as JOINs or window functions for better execution plans.
  • SELECT clause subqueries shine for readability in simple cases, but understanding when to reach for alternatives separates competent SQL from production-ready SQL.

What Is a Subquery in the SELECT Clause?

A subquery in the SELECT clause is a query nested inside the column list of your main query. Unlike subqueries in WHERE or FROM clauses, these must return exactly one value—a single row with a single column. That’s why they’re called scalar subqueries.

You use them when you need to compute or fetch a value for each row in your result set. Think of scenarios like: “Show me each employee alongside their department’s average salary” or “Display each order with the customer’s total lifetime purchases.” The subquery runs in the context of each row, giving you a derived column without restructuring your entire query.

Basic Syntax and Structure

The syntax is straightforward. You place a complete SELECT statement inside parentheses within your column list:

SELECT 
    column1,
    column2,
    (SELECT some_value FROM other_table WHERE condition) AS derived_column
FROM main_table;

The subquery can reference columns from the outer query (correlated) or stand alone (non-correlated). Here’s a practical example that counts related records:

SELECT 
    c.customer_id,
    c.customer_name,
    c.signup_date,
    (SELECT COUNT(*) 
     FROM orders o 
     WHERE o.customer_id = c.customer_id) AS total_orders
FROM customers c
ORDER BY total_orders DESC;

This query returns each customer with their order count. The subquery references c.customer_id from the outer query, making it a correlated subquery—it executes once per customer row.

Notice the alias AS total_orders. Always alias your subquery columns. Without an alias, you’ll get an auto-generated column name that’s database-dependent and usually ugly.

Correlated vs. Non-Correlated Subqueries

Understanding this distinction is critical for both correctness and performance.

Non-correlated subqueries don’t reference the outer query. They execute once, return a single value, and that value gets used for every row:

-- Non-correlated: calculates once, applies to all rows
SELECT 
    product_id,
    product_name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price_all_products,
    price - (SELECT AVG(price) FROM products) AS price_vs_average
FROM products;

The database executes SELECT AVG(price) FROM products exactly once. Every row gets the same avg_price_all_products value.

Correlated subqueries reference columns from the outer query. They execute once per row:

-- Correlated: calculates for each row based on that row's category
SELECT 
    product_id,
    product_name,
    category_id,
    price,
    (SELECT AVG(price) 
     FROM products p2 
     WHERE p2.category_id = p1.category_id) AS avg_price_in_category,
    price - (SELECT AVG(price) 
             FROM products p2 
             WHERE p2.category_id = p1.category_id) AS price_vs_category_avg
FROM products p1;

Here, the subquery runs for each product, calculating the average price within that product’s category. A table with 10,000 products means 10,000 subquery executions.

Common Use Cases

Calculating Per-Row Aggregates

The most common use case is comparing individual rows against group aggregates:

SELECT 
    e.employee_id,
    e.employee_name,
    e.department_id,
    e.salary,
    (SELECT AVG(salary) 
     FROM employees e2 
     WHERE e2.department_id = e.department_id) AS dept_avg_salary,
    ROUND(e.salary / (SELECT AVG(salary) 
                       FROM employees e2 
                       WHERE e2.department_id = e.department_id) * 100, 1) AS pct_of_dept_avg
FROM employees e
ORDER BY e.department_id, pct_of_dept_avg DESC;

This shows each employee’s salary as a percentage of their department average—useful for compensation analysis.

When you need one specific value from a related table:

SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    (SELECT c.customer_name 
     FROM customers c 
     WHERE c.customer_id = o.customer_id) AS customer_name,
    (SELECT c.credit_limit 
     FROM customers c 
     WHERE c.customer_id = o.customer_id) AS customer_credit_limit
FROM orders o
WHERE o.order_date >= '2024-01-01';

This works, but it’s a code smell. Two subqueries hitting the same table for the same row screams “use a JOIN instead.” We’ll address this shortly.

Computing Ratios and Percentages

Calculate how individual records relate to totals:

SELECT 
    o.order_id,
    o.customer_id,
    o.total_amount,
    (SELECT SUM(total_amount) 
     FROM orders o2 
     WHERE o2.customer_id = o.customer_id) AS customer_lifetime_value,
    ROUND(o.total_amount / (SELECT SUM(total_amount) 
                             FROM orders o2 
                             WHERE o2.customer_id = o.customer_id) * 100, 2) AS pct_of_lifetime_value
FROM orders o
WHERE o.order_date >= '2024-01-01';

This shows each 2024 order as a percentage of that customer’s total spending.

Performance Considerations

Here’s the uncomfortable truth: correlated subqueries in the SELECT clause can be performance killers. Each row in your outer query triggers a separate subquery execution. With proper indexing, the database might optimize this reasonably well. Without it, you’re looking at O(n×m) complexity where n is outer rows and m is inner table size.

Warning signs your subquery is hurting performance:

  • Query time scales linearly (or worse) with result set size
  • Execution plan shows “nested loops” with high row estimates
  • The subquery’s WHERE clause doesn’t use indexed columns

Indexing is your first defense. If your correlated subquery filters on customer_id, ensure that column is indexed:

-- Make sure this index exists
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Rewriting as a JOIN often performs better. Here’s a correlated subquery and its JOIN equivalent:

-- Correlated subquery approach (potentially slow)
SELECT 
    e.employee_id,
    e.employee_name,
    e.salary,
    (SELECT d.department_name 
     FROM departments d 
     WHERE d.department_id = e.department_id) AS department_name,
    (SELECT AVG(e2.salary) 
     FROM employees e2 
     WHERE e2.department_id = e.department_id) AS dept_avg_salary
FROM employees e;

-- JOIN approach (usually faster)
SELECT 
    e.employee_id,
    e.employee_name,
    e.salary,
    d.department_name,
    dept_stats.avg_salary AS dept_avg_salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_stats ON dept_stats.department_id = e.department_id;

The JOIN version calculates department averages once per department, not once per employee. On a company with 50 departments and 10,000 employees, that’s 50 calculations versus 10,000.

Alternatives: JOINs and Window Functions

Modern SQL gives you three tools for per-row calculations. Here’s the same result achieved three ways:

-- Method 1: Correlated subquery
SELECT 
    e.employee_id,
    e.employee_name,
    e.department_id,
    e.salary,
    (SELECT AVG(e2.salary) 
     FROM employees e2 
     WHERE e2.department_id = e.department_id) AS dept_avg
FROM employees e;

-- Method 2: JOIN with derived table
SELECT 
    e.employee_id,
    e.employee_name,
    e.department_id,
    e.salary,
    d.dept_avg
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS dept_avg
    FROM employees
    GROUP BY department_id
) d ON d.department_id = e.department_id;

-- Method 3: Window function
SELECT 
    employee_id,
    employee_name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;

When to use each:

Subqueries work well for simple, one-off calculations where readability matters more than performance. They’re also useful when you need conditional logic that’s awkward to express in JOINs.

JOINs are your workhorse for production code. They give the query optimizer more flexibility, typically result in better execution plans, and make the data relationships explicit.

Window functions are ideal when you need multiple aggregations over the same partition, or when you want to preserve all rows while adding aggregate calculations. They’re often the most elegant solution and perform excellently.

My default recommendation: start with window functions for analytical queries, use JOINs for transactional queries, and reserve SELECT clause subqueries for quick ad-hoc analysis or cases where they genuinely improve readability.

Summary

Subqueries in the SELECT clause are a powerful tool for adding computed columns to your results. They must return scalar values—one row, one column—and they execute in the context of each outer row when correlated.

Use them when you need per-row calculations and the query remains readable. But stay alert to performance implications: correlated subqueries execute repeatedly, and large datasets will punish naive implementations. Know your alternatives—JOINs give optimizers more room to work, and window functions often express the same logic more elegantly.

The best SQL developers don’t just know how to write subqueries; they know when not to.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.