SQL Subqueries Explained

Learn how subqueries can help you break down complex SQL queries into smaller, manageable components.

What is a Subquery?

A subquery is a SQL query nested inside another query. It allows you to retrieve intermediate results that can be used in filtering, calculations, or even table creation.

Subqueries can be used in SELECT, FROM, or WHERE clauses to simplify data retrieval.

Basic Subquery

A simple subquery filters the results based on another query's output:

-- Basic Subquery
SELECT order_id, customer_id, order_total
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE signup_date >= CURRENT_DATE - INTERVAL '1 year'
);

Correlated Subqueries

Correlated subqueries execute once per row in the outer query and are useful for row-wise comparisons:

-- Correlated Subquery
SELECT order_id, customer_id, order_total
FROM orders o
WHERE order_total > (
    SELECT AVG(order_total)
    FROM orders
    WHERE customer_id = o.customer_id
);

Nested Subqueries

Nested subqueries are used to pass results from one query to another, often replacing joins:

-- Nested Subquery
SELECT product_name, category_id, price
FROM products
WHERE category_id = (
    SELECT category_id
    FROM categories
    WHERE category_name = 'Electronics'
);