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.

What You'll Learn

By the end of this lesson you will write a query that answers a question about a set by first asking a smaller question about it — "which products were never ordered?" — and you will be able to say which of the three ways to write that question is safe when the data contains a null. That last part is not a detail: one of them returns the right answer, one returns nothing at all, and neither of them reports an error.

This lesson is what lets the capstone's reports ask conditional questions — which customers ordered nothing this month, which products outsell their category average — without building a new table for each one. You arrive here able to collapse rows into group summaries with GROUP BY and HAVING (Aggregation and Grouping); what is new is feeding one query's answer into another query's condition.

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.

"Scalar" is a promise you are making, not one the engine checks. If the inner query returns several rows, SQLite does not complain — it silently takes the first row it happens to produce and discards the rest. (SELECT name FROM employees) against this table yields one name, chosen by whatever order the scan produced, and a query built on it looks perfectly healthy. If the inner query returns no rows at all, the subquery is NULL, which prints as None and makes every comparison against it neither true nor false. So a scalar subquery should be one you can argue is scalar: an aggregate like AVG or MAX always returns exactly one row, which is why those are the safe shapes.

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. In this data every product_id is a real id, so NOT IN behaves the way it reads: it returns the products the list does not contain.

When one of these values comes from a user

The subqueries above take their values from other tables, which is safe by construction. The moment a value comes from outside — a category a shopper picked, a minimum price typed into a search box — the same rule you met in Filtering and Sorting applies, and it applies to the inner query exactly as it does to the outer one. Write the statement once with a placeholder and hand the value over separately, as a parameterized query:

SELECT name, price FROM products
WHERE id IN (SELECT product_id FROM order_items WHERE order_date >= ?)
  AND category = ?;

You cannot run this one. The Run button sends a script of literal SQL with no program around it, and binding a parameter is something a program does through its database driver — so this fence is a shape to recognise rather than a statement to execute. Two caveats travel with it, and nesting does not change either:

• The binding has to happen in the driver. A helper that escapes the value and splices it into your string is still string-building, however deep in the query the value sits. Hand the value to the driver.
• A placeholder stands for a value, never for an identifier. You cannot bind a table or column name, so a subquery whose table is chosen at runtime, or an ORDER BY whose column comes from a request, needs an allow-list of names you wrote by hand, with everything else rejected.

One thing nesting does change: a subquery gives an injected fragment more places to do damage, because a value glued into an inner WHERE can close that subquery and rewrite the outer one. The defense is the same and so is the discipline — every value from outside is bound, wherever in the query it appears.

What NOT IN does when the list contains a NULL

The moment the subquery's column is nullable, NOT IN stops being the inverse of IN. Commit an answer to the block below before you run anything — the fence after it is the reveal.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, product_id INTEGER, quantity INTEGER);
INSERT INTO products VALUES (1,'Laptop'),(2,'Mouse'),(3,'Desk'),(4,'Chair');
INSERT INTO order_items VALUES (1,1,2),(2,2,5),(3,NULL,1);
SELECT name FROM products
WHERE id NOT IN (SELECT product_id FROM order_items)
ORDER BY name;

Now run both spellings of the question and compare them:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, product_id INTEGER, quantity INTEGER);
INSERT INTO products VALUES (1,'Laptop'),(2,'Mouse'),(3,'Desk'),(4,'Chair');
INSERT INTO order_items VALUES (1,1,2),(2,2,5),(3,NULL,1);

-- Products never ordered, asked with NOT IN
SELECT name FROM products
WHERE id NOT IN (SELECT product_id FROM order_items)
ORDER BY name;

-- The same question, asked with NOT EXISTS
SELECT name FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items o WHERE o.product_id = p.id)
ORDER BY name;

The first query returns nothing — an empty result, no error, exit 0. The second returns Chair and Desk, which is the true answer. Two spellings of one question, and one of them silently answers "none".

The reason is three-valued logic. id NOT IN (1, 2, NULL) expands to id <> 1 AND id <> 2 AND id <> NULL, and that last comparison is never true and never false — it is NULL, because nothing can be known to differ from an unknown value. NULL AND anything-true is NULL, WHERE keeps only rows where the condition is true, and so every row is discarded. Run SELECT 3 NOT IN (1, NULL); on its own and the lane prints None.

Two safe forms, and you should prefer them by habit rather than after being bitten:

• NOT EXISTS, which asks whether a matching row exists and is therefore immune — a null product_id simply does not match, rather than poisoning the comparison.
• NOT IN (SELECT product_id FROM order_items WHERE product_id IS NOT NULL), which removes the null before the comparison sees it. Measured on the data above, this also returns Chair and Desk.

IN itself is not affected in the same way: a null in the list can only ever turn a false into a NULL, and a row that was going to match still matches.

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.

The correlation is the entire difference between a subquery that asks one question and one that asks a question per row, and it is easy to drop by accident. A subquery that names no outer column runs once, produces one answer, and pastes that same answer beside every row — which looks like a per-row result and is not.

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 NOT NULL);
INSERT INTO departments VALUES (1,'Engineering'),(2,'Marketing'),(3,'Facilities');
INSERT INTO employees VALUES (1,'Ada',1,95000),(2,'Bo',1,88000),(3,'Cy',2,72000);
SELECT d.name AS department, (SELECT MAX(e.salary) FROM employees e) AS top_salary
FROM departments d
ORDER BY d.name;

3 row(s) affected
3 row(s) affected
department | top_salary
-----------------------
Engineering | 95000
Facilities | None
Marketing | 72000

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.

Two variations to try on the first query above. Predict each before you run it.

1. Raise the threshold from 8000 to 12000. The result comes back empty — header and dash rule, no rows — because David's 12000 is the largest total and the test is strictly greater than. An empty result is a result: the query succeeded and the answer is "nobody".
2. Move the filter inside the derived table, as HAVING SUM(amount) > 8000, and delete the outer WHERE. The output is identical. That is worth seeing once, because it shows the derived table and the outer query are two places to say the same thing — and the reason to prefer one is readability, not behaviour.

Ordering the clauses around a subquery

Every arrangement below runs against the sales table, which is already given.

CREATE TABLE sales (id INTEGER PRIMARY KEY, rep TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO sales VALUES (1,'Ada',5000),(2,'Ada',3200),(3,'Bo',4500),(4,'Bo',6100),(5,'Cy',2800);

CREATE TABLE staff (id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER);
INSERT INTO staff VALUES (1,'Ada',NULL),(2,'Bo',1),(3,'Cy',1),(4,'Di',2);
SELECT id, name FROM staff
WHERE id NOT IN (SELECT manager_id FROM staff)
ORDER BY id;

4 row(s) affected
id | name
---------
3 | Cy
4 | Di

Try It Yourself

Build the never-ordered report — the question that opens most inventory clean-ups, and the one the NULL trap is waiting in. The starter runs, exits cleanly, and reports that every product has been ordered, which is not true of this data.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_cents INTEGER NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, product_id INTEGER, quantity INTEGER NOT NULL);
INSERT INTO products VALUES (1,'Laptop',99900),(2,'Mouse',2499),(3,'Desk',29999),(4,'Chair',19999),(5,'Lamp',4999);
INSERT INTO order_items VALUES (1,1,2),(2,2,5),(3,NULL,1),(4,1,1);
-- TODO 1: the order_items table has a row with a NULL product_id, which
--         empties any NOT IN over that column. Rewrite the condition so a
--         null order line cannot silence the whole result. Desk, Chair and
--         Lamp must come back.
-- TODO 2: sort by price_cents descending, breaking ties by name ascending.
SELECT name, price_cents
FROM products
WHERE id NOT IN (SELECT product_id FROM order_items)
ORDER BY name;

5 row(s) affected
4 row(s) affected
name | price_cents
------------------
Desk | 29999
Chair | 19999
Lamp | 4999

Once it passes, try two variations and predict each before you run it.

1. Delete the order line with the NULL product_id and put the original NOT IN back. The three products come back correctly, because with no null in the list the chain of inequalities can be true. This is why the bug is so durable: the query is right until one row of data changes, and the data change is somewhere else entirely.
2. Change NOT EXISTS to EXISTS, keeping everything else. You get the complement — Laptop and Mouse, the two products that were ordered — sorted the same way. The pair is worth seeing together, because it shows the condition tests a row's existence and the NOT is doing all the inverting.

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;

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Ada'),(2,'Bo'),(3,'Cy');
INSERT INTO orders VALUES (1,1),(2,1),(3,2);

SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) ORDER BY name;
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders) ORDER BY name;
SELECT c.name FROM customers c JOIN orders o ON o.customer_id = c.id ORDER BY c.name;
SELECT DISTINCT c.name FROM customers c JOIN orders o ON o.customer_id = c.id ORDER BY c.name;

Practice Exercises

Try these on your own:

1. Find all products priced above the average product price
2. List customers who placed orders totaling more than the overall average order total
3. Use a subquery in FROM to find the region with the highest total sales
4. Find employees whose salary is the highest in their department (correlated subquery)
5. 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 — and if the inner query returns several rows, SQLite takes the first rather than complaining
• Use IN with subqueries to check if a value belongs to a result set
• NOT IN over a nullable column returns nothing at all, silently: use NOT EXISTS, or exclude the nulls inside the subquery
• Correlated subqueries reference the outer query and run once per outer row; one that names no outer column runs once and repeats its answer
• 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.