Subqueries are queries nested inside other queries. They allow you to perform complex operations by breaking them into smaller, manageable parts.

Basic Subquery Syntax

SELECT column
FROM table
WHERE column OPERATOR (SELECT column FROM table WHERE condition);

Subqueries in WHERE Clause

Single Value Subquery

-- Find employees earning more than average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Find the most expensive product's details
SELECT *
FROM products
WHERE price = (SELECT MAX(price) FROM products);

Multiple Values with IN

-- Find customers who placed orders
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- Find products never ordered
SELECT name
FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);

EXISTS Subquery

-- Customers with at least one order
SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.id
);

-- Products not in any order
SELECT name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 
    FROM order_items oi 
    WHERE oi.product_id = p.id
);

Subqueries in FROM Clause

-- Average order value per customer
SELECT 
    customer_id,
    avg_order_value
FROM (
    SELECT 
        customer_id,
        AVG(total) as avg_order_value
    FROM orders
    GROUP BY customer_id
) as customer_averages
WHERE avg_order_value > 100;

-- Rank customers by total spending
SELECT 
    customer_id,
    total_spent,
    RANK() OVER (ORDER BY total_spent DESC) as rank
FROM (
    SELECT 
        customer_id,
        SUM(total) as total_spent
    FROM orders
    GROUP BY customer_id
) as customer_totals;

Subqueries in SELECT Clause

-- Add computed column with subquery
SELECT 
    name,
    price,
    (SELECT AVG(price) FROM products) as avg_price,
    price - (SELECT AVG(price) FROM products) as diff_from_avg
FROM products;

-- Count related records
SELECT 
    c.name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) as order_count
FROM customers c;

Correlated Subqueries

The inner query references the outer query:

-- Employees earning more than department average
SELECT name, department, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

-- Most recent order per customer
SELECT *
FROM orders o1
WHERE order_date = (
    SELECT MAX(order_date)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

Common Table Expressions (CTEs)

More readable alternative to subqueries:

-- Using WITH clause
WITH high_value_customers AS (
    SELECT customer_id, SUM(total) as total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total) > 1000
)
SELECT 
    c.name,
    hvc.total_spent
FROM customers c
JOIN high_value_customers hvc ON c.id = hvc.customer_id;

-- Multiple CTEs
WITH 
monthly_sales AS (
    SELECT 
        EXTRACT(MONTH FROM order_date) as month,
        SUM(total) as revenue
    FROM orders
    GROUP BY EXTRACT(MONTH FROM order_date)
),
avg_monthly AS (
    SELECT AVG(revenue) as avg_revenue
    FROM monthly_sales
)
SELECT 
    month,
    revenue,
    (SELECT avg_revenue FROM avg_monthly) as average,
    revenue - (SELECT avg_revenue FROM avg_monthly) as diff
FROM monthly_sales;

Practical Examples

Top N Per Group

-- Top 3 products per category by sales
SELECT *
FROM (
    SELECT 
        category,
        product_name,
        total_sales,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY total_sales DESC
        ) as rank
    FROM (
        SELECT 
            p.category,
            p.name as product_name,
            SUM(oi.quantity * oi.price) as total_sales
        FROM products p
        JOIN order_items oi ON p.id = oi.product_id
        GROUP BY p.category, p.name
    ) as product_sales
) as ranked
WHERE rank <= 3;

Percentage of Total

SELECT 
    category,
    SUM(revenue) as category_revenue,
    ROUND(100.0 * SUM(revenue) / (
        SELECT SUM(revenue) FROM sales
    ), 2) as percentage
FROM sales
GROUP BY category;

Finding Duplicates

SELECT *
FROM employees
WHERE email IN (
    SELECT email
    FROM employees
    GROUP BY email
    HAVING COUNT(*) > 1
);

Running Comparison

-- Compare each month to previous year same month
SELECT 
    current_year.month,
    current_year.revenue as current_revenue,
    (
        SELECT revenue
        FROM monthly_sales prev_year
        WHERE prev_year.year = current_year.year - 1
        AND prev_year.month = current_year.month
    ) as prev_year_revenue
FROM monthly_sales current_year
WHERE current_year.year = 2025;

Performance Tips

-- Use EXISTS instead of IN for large datasets
-- Slower
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE city = 'NYC');

-- Faster
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM customers c 
    WHERE c.id = o.customer_id AND c.city = 'NYC'
);

-- Use JOINs when possible (often more efficient)
SELECT o.*
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'NYC';

Summary

Subqueries can appear in WHERE, FROM, and SELECT clauses
Use IN for multiple value comparisons
Use EXISTS for checking existence (often faster)
Correlated subqueries reference outer query columns
CTEs (WITH) make complex queries more readable
Consider JOINs as an alternative for better performance

Settings

Appearance