Subqueries

A subquery is a query nested inside another query. Think of it as asking the database to answer a smaller question first, then using that answer to solve a bigger one. Subqueries are one of the most versatile tools in SQL, letting you build complex logic step by step.

Scalar Subqueries

A scalar subquery returns a single value — one row with one column. You can use it anywhere you would use a single value, such as in a WHERE clause or SELECT list.

-- Create employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary INTEGER
);

INSERT INTO employees (id, name, department, salary)
VALUES
    (1, 'Alice Chen', 'Engineering', 95000),
    (2, 'Bob Martinez', 'Engineering', 88000),
    (3, 'Carol White', 'Marketing', 72000),
    (4, 'David Kim', 'Marketing', 68000),
    (5, 'Eva Brown', 'Sales', 65000),
    (6, 'Frank Lee', 'Sales', 71000),
    (7, 'Grace Patel', 'Engineering', 102000),
    (8, 'Henry Wilson', 'Marketing', 78000);

-- Find employees who earn more than the company average
SELECT name, department, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

-- Show each employee's salary compared to the average
SELECT
    name,
    department,
    salary,
    (SELECT ROUND(AVG(salary), 0) FROM employees) AS company_avg,
    salary - (SELECT ROUND(AVG(salary), 0) FROM employees) AS diff_from_avg
FROM employees
ORDER BY diff_from_avg DESC;

The inner query (SELECT AVG(salary) FROM employees) runs first and produces a single number. The outer query then uses that number just like a constant.

IN with Subqueries

When a subquery returns multiple rows with a single column, you can use it with IN to check membership.

-- Create products and order_items tables
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price REAL
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    product_id INTEGER,
    quantity INTEGER,
    order_date TEXT
);

INSERT INTO products (id, name, category, price)
VALUES
    (1, 'Laptop', 'Electronics', 999.99),
    (2, 'Mouse', 'Electronics', 24.99),
    (3, 'Desk', 'Furniture', 299.99),
    (4, 'Chair', 'Furniture', 199.99),
    (5, 'Headphones', 'Electronics', 149.99),
    (6, 'Lamp', 'Furniture', 49.99);

INSERT INTO order_items (id, product_id, quantity, order_date)
VALUES
    (1, 1, 2, '2025-01-10'),
    (2, 2, 5, '2025-01-11'),
    (3, 4, 1, '2025-01-12'),
    (4, 1, 1, '2025-01-13');

-- Products that have been ordered at least once
SELECT name, category, price
FROM products
WHERE id IN (SELECT DISTINCT product_id FROM order_items);

-- Products that have NEVER been ordered
SELECT name, category, price
FROM products
WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items);

The subquery produces a list of product IDs, and the outer query checks each product against that list. NOT IN gives you the inverse — items not in the list.

EXISTS and Correlated Subqueries

A correlated subquery references the outer query. It runs once for each row in the outer query, which makes it more powerful (but potentially slower) than a simple subquery.

EXISTS returns TRUE if the subquery produces any rows at all.

-- Create customers and orders tables
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    city TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL,
    order_date TEXT
);

INSERT INTO customers (id, name, email, city)
VALUES
    (1, 'Alice', 'alice@example.com', 'New York'),
    (2, 'Bob', 'bob@example.com', 'London'),
    (3, 'Carol', 'carol@example.com', 'Paris'),
    (4, 'David', 'david@example.com', 'Tokyo');

INSERT INTO orders (id, customer_id, total, order_date)
VALUES
    (1, 1, 150.00, '2025-01-10'),
    (2, 1, 89.99, '2025-01-15'),
    (3, 2, 250.00, '2025-01-12'),
    (4, 3, 75.00, '2025-01-20');

-- Find customers who have placed at least one order
SELECT name, city
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

-- Find customers who have NOT placed any orders
SELECT name, city
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

-- Find customers whose largest order exceeds $100
SELECT name, city
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.total > 100
);

Notice how o.customer_id = c.id references c from the outer query. That's what makes it correlated. The database evaluates the subquery for each customer row. SELECT 1 is a convention — EXISTS only cares whether any rows are returned, not what's in them.

Subqueries in FROM (Derived Tables)

You can use a subquery in the FROM clause, creating a temporary result set (sometimes called a derived table) that you query against.

-- Create a sales table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    salesperson TEXT NOT NULL,
    region TEXT NOT NULL,
    amount REAL,
    sale_date TEXT
);

INSERT INTO sales (id, salesperson, region, amount, sale_date)
VALUES
    (1, 'Alice', 'North', 5000, '2025-01-05'),
    (2, 'Alice', 'North', 3200, '2025-01-12'),
    (3, 'Bob', 'South', 4500, '2025-01-08'),
    (4, 'Bob', 'South', 6100, '2025-01-15'),
    (5, 'Carol', 'North', 2800, '2025-01-10'),
    (6, 'Carol', 'North', 3900, '2025-01-18'),
    (7, 'David', 'South', 7200, '2025-01-06'),
    (8, 'David', 'South', 4800, '2025-01-20');

-- First, create a summary per salesperson, then find the top performers
SELECT
    summary.salesperson,
    summary.total_sales,
    summary.num_deals
FROM (
    SELECT
        salesperson,
        SUM(amount) AS total_sales,
        COUNT(*) AS num_deals
    FROM sales
    GROUP BY salesperson
) AS summary
WHERE summary.total_sales > 8000
ORDER BY summary.total_sales DESC;

-- Compare each salesperson to the regional average
SELECT
    s.salesperson,
    s.region,
    SUM(s.amount) AS personal_total,
    regional.avg_total AS regional_avg
FROM sales s
INNER JOIN (
    SELECT region, AVG(total) AS avg_total
    FROM (
        SELECT salesperson, region, SUM(amount) AS total
        FROM sales
        GROUP BY salesperson, region
    )
    GROUP BY region
) AS regional ON s.region = regional.region
GROUP BY s.salesperson, s.region
ORDER BY personal_total DESC;

Derived tables are especially useful when you need to aggregate data and then filter or join on the results.

Subqueries in SELECT

You can embed a subquery directly in the SELECT clause to compute a value for each row.

-- Create departments and employees tables
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary INTEGER
);

INSERT INTO departments (id, name)
VALUES (1, 'Engineering'), (2, 'Marketing'), (3, 'Sales');

INSERT INTO employees (id, name, department_id, salary)
VALUES
    (1, 'Alice', 1, 95000),
    (2, 'Bob', 1, 88000),
    (3, 'Carol', 2, 72000),
    (4, 'David', 2, 68000),
    (5, 'Eva', 3, 65000),
    (6, 'Frank', 3, 71000),
    (7, 'Grace', 1, 102000);

-- Show each department with its employee count and average salary
SELECT
    d.name AS department,
    (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) AS headcount,
    (SELECT ROUND(AVG(e.salary), 0) FROM employees e WHERE e.department_id = d.id) AS avg_salary,
    (SELECT MAX(e.salary) FROM employees e WHERE e.department_id = d.id) AS top_salary
FROM departments d
ORDER BY avg_salary DESC;

Practice Exercises

Try these on your own:

Find all products priced above the average product price
List customers who placed orders totaling more than the overall average order total
Use a subquery in FROM to find the region with the highest total sales
Find employees whose salary is the highest in their department (correlated subquery)
Use EXISTS to find departments that have at least 3 employees

Key Takeaways

Scalar subqueries return a single value and can be used in SELECT, WHERE, or HAVING
Use IN with subqueries to check if a value belongs to a result set
NOT IN finds values absent from a subquery's results
Correlated subqueries reference the outer query and run once per outer row
EXISTS checks whether a subquery returns any rows at all
Subqueries in FROM create derived tables you can query against
Start simple and build up — test each subquery independently before nesting it

Pro Tip: If you find yourself writing deeply nested subqueries (3+ levels), consider using Common Table Expressions (CTEs) instead. They're easier to read and debug. You'll learn about CTEs in the Views and CTEs lesson!

Next Steps

You can now write sophisticated queries that nest logic inside other queries. But as your tables grow to thousands or millions of rows, query speed matters. The next lesson covers indexes and performance — how the database engine finds your data under the hood, how to read execution plans, and when to create indexes that turn slow queries into fast ones.