Subqueries

Subquery in WHERE Clause

A subquery in the WHERE clause filters rows based on the result of another query. The most common pattern uses IN to check if a value exists in a set returned by the subquery.

-- Find all customers who have placed an order
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
);

You can also use comparison operators like =, >, <, >=, <= with scalar subqueries that return a single value:

-- Find products priced above the average price
SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

The NOT IN variant finds rows that do not match any value in the subquery result. Be cautious: if the subquery returns any NULL values, NOT IN will return no results. Use NOT EXISTS as a safer alternative in those cases.

-- Find customers who have never placed an order
SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE customer_id IS NOT NULL
);

Subqueries in WHERE clauses are evaluated once for the outer query, making them straightforward to understand and debug. They are particularly useful when you need to filter based on aggregated data from another table.

Subquery in FROM Clause

When you place a subquery in the FROM clause, it creates a derived table (also called an inline view). The subquery executes first, and its result set becomes a temporary table that the outer query can reference.

-- Get average order total per customer, then find customers above the overall average
SELECT customer_id, avg_order_total
FROM (
    SELECT customer_id, AVG(total_amount) AS avg_order_total
    FROM orders
    GROUP BY customer_id
) AS customer_averages
WHERE avg_order_total > (
    SELECT AVG(total_amount)
    FROM orders
);

Derived tables must always have an alias. This alias is used to reference the subquery result in the outer query. The subquery in FROM is especially useful when you need to perform aggregations on already-aggregated data.

-- Rank departments by their total sales using a derived table
SELECT department_id, total_sales,
       RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM (
    SELECT department_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY department_id
) AS department_sales;

One limitation is that most databases do not allow you to reference aliases defined in the same FROM clause within the subquery. You may need to duplicate the subquery or use a CTE instead. Derived tables are materialized as temporary result sets, so they can be useful for breaking complex queries into logical steps.

Subquery in SELECT Clause

A subquery in the SELECT list must return a single value (scalar). It acts as a computed column, adding supplementary information to each row of the outer query.

-- Show each order with the customer's total order count
SELECT 
    order_id,
    customer_id,
    total_amount,
    (
        SELECT COUNT(*)
        FROM orders AS o2
        WHERE o2.customer_id = o1.customer_id
    ) AS total_orders_by_customer
FROM orders AS o1;

This pattern is useful for adding context to each row without using JOINs. However, correlated subqueries in the SELECT list execute once per row, which can impact performance on large datasets.

-- Display product names alongside the number of times each was ordered
SELECT 
    p.product_id,
    p.product_name,
    p.price,
    (
        SELECT SUM(oi.quantity)
        FROM order_items AS oi
        WHERE oi.product_id = p.product_id
    ) AS total_quantity_sold
FROM products AS p;

For performance-sensitive scenarios, consider using window functions or JOINs instead. The SELECT subquery is best suited for reporting and ad-hoc queries where readability matters more than raw speed. Always ensure the subquery returns exactly one value; otherwise, the query will fail.

Scalar Subqueries

A scalar subquery returns exactly one row and one column. It can be used anywhere a single value is expected: in SELECT, WHERE, HAVING, and JOIN conditions.

-- Compare each employee's salary to the company average
SELECT 
    employee_name,
    salary,
    salary - (
        SELECT AVG(salary)
        FROM employees
    ) AS difference_from_average
FROM employees;

Scalar subqueries in WHERE clauses work with comparison operators:

-- Find orders placed on the most recent date
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date = (
    SELECT MAX(order_date)
    FROM orders
);

If a scalar subquery returns more than one row, most databases will throw an error. If it returns no rows, the result is NULL. You can use COALESCE to handle this:

SELECT 
    e.employee_name,
    e.salary,
    COALESCE(
        (SELECT AVG(salary) FROM employees WHERE department = e.department),
        0
    ) AS dept_avg_salary
FROM employees AS e;

Scalar subqueries are also valid in JOIN ON clauses and CASE expressions. They are the simplest form of subquery and are often optimized well by database engines. When the same scalar subquery appears multiple times, consider extracting it into a CTE to avoid redundant computation.