How to Write SQL Subqueries: A Practical Guide With Real Examples

A subquery is a SELECT statement nested inside another SQL statement. The nested query is called the inner query or subquery, and the statement it sits inside is called the outer query or main query. The inner query runs first (or, for one specific kind covered below, runs once for every row the outer query touches), and its result feeds into the outer query as a single value, a list of values, or an entire table to query against.

Subqueries let you answer questions a single flat SELECT cannot: “show me each product’s price next to the average price across all products,” “find every customer who spent more than $150,” “list the products nobody has ever ordered.” Once you can read and write them, a whole class of SQL problems that look impossible with one SELECT statement become straightforward.

This tutorial builds a small SQLite database from scratch and works through every major subquery pattern against it, in order: filtering rows, computing a value, building a table to query against, correlating a subquery to each row of the outer query, and checking existence with EXISTS. It then covers two subquery mistakes that catch beginners, and more than a few experienced developers, off guard, and shows you how to verify a subquery is actually doing what you think it is before you trust it. Every query shown below was run against a real database while writing this post using Python’s built-in sqlite3 module; the output shown is copied directly from that run, not invented.

What You Will Learn

By the end of this tutorial you will be able to:

• Recognize the difference between a non-correlated subquery (evaluated once, independent of the outer query) and a correlated subquery (re-evaluated once per row of the outer query)
• Use a subquery as a filter, a derived column, and a derived table
• Use EXISTS and NOT EXISTS to test for the presence or absence of related rows
• Spot and avoid the classic NOT IN plus NULL bug and the silent multi-row scalar subquery bug
• Check what a subquery is actually doing, both by testing it in isolation and by reading a query plan

Prerequisites

• Basic, working knowledge of SQL: SELECT, FROM, WHERE, JOIN, GROUP BY, and ORDER BY. This tutorial assumes you can already write a simple query; it does not re-teach the basics
• Python 3.9 or newer installed and on your PATH. This tutorial used Python 3.13.14, but any recent Python 3 works, because the only library used is sqlite3, which ships with Python itself, on Windows, macOS, and Linux alike. Nothing else needs to be installed
• A terminal and a text editor. No database server, Docker container, or account of any kind is required; SQLite reads and writes a single file on disk

Step 1: Set Up a Practice Database

To make the examples concrete, this tutorial uses a small, three-table database for a fictional online shop: customers, products, and orders. Create a new folder, and inside it a file named setup_db.py:

import sqlite3
import os

DB_PATH = "shop.db"
if os.path.exists(DB_PATH):
    os.remove(DB_PATH)

conn = sqlite3.connect(DB_PATH)
conn.execute("PRAGMA foreign_keys = ON;")
cur = conn.cursor()

cur.executescript("""
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    city        TEXT NOT NULL
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    category   TEXT NOT NULL,
    price      REAL NOT NULL
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_id  INTEGER NOT NULL,
    quantity    INTEGER NOT NULL,
    order_date  TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
""")

customers = [
    (1, "Maria Chen", "Austin"),
    (2, "Devon Brooks", "Austin"),
    (3, "Priya Patel", "Seattle"),
    (4, "Sam Okafor", "Denver"),
    (5, "Lena Ivanova", "Seattle"),
    (6, "Noah Kim", "Denver"),
]
cur.executemany("INSERT INTO customers VALUES (?,?,?)", customers)

products = [
    (101, "Mechanical Keyboard", "Peripherals", 89.00),
    (102, "USB-C Dock", "Peripherals", 45.00),
    (103, "27in Monitor", "Displays", 249.00),
    (104, "Webcam 1080p", "Peripherals", 39.00),
    (105, "Standing Desk", "Furniture", 399.00),
    (106, "Desk Lamp", "Furniture", 29.00),
]
cur.executemany("INSERT INTO products VALUES (?,?,?,?)", products)

orders = [
    (1, 1, 101, 1, "2026-07-01"),
    (2, 1, 103, 1, "2026-07-03"),
    (3, 2, 102, 2, "2026-07-05"),
    (4, 3, 104, 1, "2026-07-06"),
    (5, 3, 103, 1, "2026-07-10"),
    (6, 3, 101, 1, "2026-07-12"),
    (7, 4, 105, 1, "2026-07-15"),
    (8, 5, 102, 1, "2026-07-18"),
    (9, None, 104, 1, "2026-07-20"),  # guest checkout, no customer account
]
cur.executemany("INSERT INTO orders VALUES (?,?,?,?,?)", orders)

conn.commit()
print("customers:", cur.execute("SELECT COUNT(*) FROM customers").fetchone()[0])
print("products:", cur.execute("SELECT COUNT(*) FROM products").fetchone()[0])
print("orders:", cur.execute("SELECT COUNT(*) FROM orders").fetchone()[0])
conn.close()

Notice order 9: its customer_id is None, which SQLite stores as SQL NULL. That represents a guest checkout, an order placed without a customer account. It looks like a small, arbitrary detail right now. Keep it in mind; it is the exact thing that breaks a common subquery pattern in Step 7.

Run it:

python setup_db.py

Expected output:

customers: 6
products: 6
orders: 9

If you see those three counts, your practice database is ready. Every example from here on reconnects to this same shop.db file, so run setup_db.py once and leave it in place.

Step 2: Filter Rows With a Subquery

The simplest and most common subquery pattern uses a subquery to build a list of values, then filters the outer query against that list with IN. This is a non-correlated subquery: it does not reference anything from the outer query, so SQLite can compute it once, on its own, before the outer query even starts.

Say you want every order placed by a customer who lives in Austin. Save this as step2_filter.py:

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()

print("--- inner subquery alone ---")
for row in cur.execute("SELECT customer_id FROM customers WHERE city = 'Austin'"):
    print(row)

print("\n--- full query: orders from Austin customers ---")
cur.execute("""
    SELECT order_id, customer_id, product_id, quantity, order_date
    FROM orders
    WHERE customer_id IN (
        SELECT customer_id FROM customers WHERE city = 'Austin'
    )
    ORDER BY order_id;
""")
cols = [d[0] for d in cur.description]
print(cols)
for row in cur.fetchall():
    print(row)

conn.close()

Run it:

python step2_filter.py

Real output:

--- inner subquery alone ---
(1,)
(2,)

--- full query: orders from Austin customers ---
['order_id', 'customer_id', 'product_id', 'quantity', 'order_date']
(1, 1, 101, 1, '2026-07-01')
(2, 1, 103, 1, '2026-07-03')
(3, 2, 102, 2, '2026-07-05')

Walk through what happened. The inner query, SELECT customer_id FROM customers WHERE city = 'Austin', ran by itself and returned two IDs: 1 and 2. The outer query never sees that SQL text; it only sees the finished list of values, as if you had typed WHERE customer_id IN (1, 2) directly. That is the key idea behind a non-correlated subquery: SQLite evaluates it once, gets a concrete answer, and substitutes that answer into the surrounding query.

This matters for a very practical reason. If you had hardcoded IN (1, 2) instead, the query would silently go stale the moment a new customer signs up in Austin. The subquery version stays correct automatically, because it re-derives “who is in Austin” from the customers table every time the query runs.

Step 3: Compute a Value With a Scalar Subquery (Derived Column)

A subquery that returns exactly one row and one column is called a scalar subquery: SQL treats its result as a single value, and you can use it almost anywhere a literal value is allowed, including inside the column list of a SELECT statement. That gives you a “derived column”: a column that exists only in the query’s output, computed on the fly, and not stored in any table.

Here is every product’s price next to the average price across all products, and how far each one is from that average. Save this as step3_derived_column.py:

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()

cur.execute("""
    SELECT
        name,
        price,
        (SELECT ROUND(AVG(price), 2) FROM products) AS avg_price,
        ROUND(price - (SELECT AVG(price) FROM products), 2) AS diff_from_avg
    FROM products
    ORDER BY price DESC;
""")
cols = [d[0] for d in cur.description]
print(cols)
for row in cur.fetchall():
    print(row)

conn.close()

Real output:

['name', 'price', 'avg_price', 'diff_from_avg']
('Standing Desk', 399.0, 141.67, 257.33)
('27in Monitor', 249.0, 141.67, 107.33)
('Mechanical Keyboard', 89.0, 141.67, -52.67)
('USB-C Dock', 45.0, 141.67, -96.67)
('Webcam 1080p', 39.0, 141.67, -102.67)
('Desk Lamp', 29.0, 141.67, -112.67)

Two things are worth calling out here. First, the subquery (SELECT AVG(price) FROM products) does not mention the outer query’s name or price columns at all; it always computes the same number (141.67, the average of all six product prices) regardless of which product row the outer query is currently looking at. That makes it non-correlated, same as Step 2, even though it appears inside the column list instead of a WHERE clause.

Second, notice the subquery appears twice, once wrapped in ROUND() for the avg_price column, and again unrounded inside the subtraction for diff_from_avg. SQL has no variables, so if you need the same computed value more than once in a query, you either repeat the subquery (as done here) or, more efficiently, compute it once in a derived table and reference it twice, which is exactly what Step 4 covers.

Step 4: Query a Subquery Like a Table (Derived Table)

A subquery placed in the FROM clause, given an alias, produces a derived table: a temporary, named result set that the rest of the query can join to, filter, and select from exactly as if it were a real table. This is the pattern to reach for when you need to aggregate first and then filter or join on the aggregated result, something a WHERE clause alone cannot do, because WHERE runs before GROUP BY.

Find every customer who has spent more than $150 in total. Save this as step4_derived_table.py:

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()

cur.execute("""
    SELECT c.name, spend.total_spent
    FROM customers c
    JOIN (
        SELECT o.customer_id, SUM(p.price * o.quantity) AS total_spent
        FROM orders o
        JOIN products p ON p.product_id = o.product_id
        WHERE o.customer_id IS NOT NULL
        GROUP BY o.customer_id
    ) AS spend ON spend.customer_id = c.customer_id
    WHERE spend.total_spent > 150
    ORDER BY spend.total_spent DESC;
""")
cols = [d[0] for d in cur.description]
print(cols)
for row in cur.fetchall():
    print(row)

conn.close()

Real output:

['name', 'total_spent']
('Sam Okafor', 399.0)
('Priya Patel', 377.0)
('Maria Chen', 338.0)

Read the inner query first, on its own: it joins orders to products to price each line item, sums that per customer with GROUP BY o.customer_id, and produces one row per customer with their total spend. Note the WHERE o.customer_id IS NOT NULL inside that inner query; it exists specifically to exclude the guest order from Step 1, since a guest has no customer_id to group by or join back to customers on.

The outer query then treats that result, aliased spend, as an ordinary table: it joins customers to it on customer_id, and filters with a normal WHERE clause on spend.total_spent. You could not write WHERE SUM(p.price * o.quantity) > 150 directly (aggregates cannot be filtered with WHERE, that is what HAVING is for), and even HAVING would need to live inside the same single-level GROUP BY query. Wrapping the aggregation in a derived table sidesteps that limitation entirely: the aggregation finishes first, and the outer query filters on its finished result like any other column.

Step 5: Correlated Subqueries: One Result Per Outer Row

Every subquery so far has been independent of the outer query: run it once, get one answer, reuse that answer. A correlated subquery is different. It references a column from the outer query inside its own WHERE clause, which means SQLite cannot compute it in advance; it has to re-run the subquery once for every row the outer query produces, each time substituting that row’s value in.

Count how many orders each customer has placed. Save this as step5_correlated.py:

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()

cur.execute("""
    SELECT
        c.customer_id,
        c.name,
        (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
    FROM customers c
    ORDER BY order_count DESC, c.customer_id;
""")
cols = [d[0] for d in cur.description]
print(cols)
for row in cur.fetchall():
    print(row)

conn.close()

Real output:

['customer_id', 'name', 'order_count']
(3, 'Priya Patel', 3)
(1, 'Maria Chen', 2)
(2, 'Devon Brooks', 1)
(4, 'Sam Okafor', 1)
(5, 'Lena Ivanova', 1)
(6, 'Noah Kim', 0)

Look closely at WHERE o.customer_id = c.customer_id inside the subquery. c.customer_id belongs to the outer query’s customers table, not the subquery’s own orders table. That reference is what makes this subquery correlated: for the “Maria Chen” row, SQLite runs the inner COUNT with c.customer_id bound to 1; for the “Noah Kim” row, it reruns the exact same inner query with c.customer_id bound to 6, which correctly returns 0, since Noah Kim has never placed an order.

The SQLite documentation puts the general rule plainly: “A correlated subquery is reevaluated each time its result is required. An uncorrelated subquery is evaluated only once and the result reused as necessary.” That single sentence is the entire mental model you need: a correlated subquery is a small piece of logic that runs once per outer row, not once total. Step 8 shows you how to see that repetition happen in a real query plan, and why it is worth knowing about before you run a correlated subquery over a large table.

Step 6: Check Existence With EXISTS and NOT EXISTS

EXISTS is a special, boolean-only operator built for exactly one job: answering “does at least one matching row exist,” without caring what columns that row contains or how many rows there are, only whether there is one or more. It is almost always written with a correlated subquery inside it, since “exists for this specific outer row” is the question you usually want answered.

Two examples: customers who have placed at least one order, and products that have never been ordered. Save this as step6_exists.py:

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()

print("--- customers with at least one order (EXISTS) ---")
cur.execute("""
    SELECT name FROM customers c
    WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
    ORDER BY c.customer_id;
""")
for row in cur.fetchall():
    print(row)

print("\n--- products never ordered (NOT EXISTS) ---")
cur.execute("""
    SELECT name FROM products p
    WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.product_id)
    ORDER BY p.product_id;
""")
for row in cur.fetchall():
    print(row)

conn.close()

Real output:

--- customers with at least one order (EXISTS) ---
('Maria Chen',)
('Devon Brooks',)
('Priya Patel',)
('Sam Okafor',)
('Lena Ivanova',)

--- products never ordered (NOT EXISTS) ---
('Desk Lamp',)

Notice the subquery inside EXISTS is SELECT 1 FROM orders o WHERE ..., not SELECT * FROM orders or SELECT order_id FROM orders. That 1 is a common, deliberate convention: since EXISTS only cares whether any row comes back, the actual selected column is thrown away, and selecting a constant makes that explicit to anyone reading the query. The SQLite documentation confirms this directly: “The number of columns in each row returned by the SELECT statement (if any) and the specific values returned have no effect on the results of the EXISTS operator.” You could write SELECT * there and get an identical result, just with unnecessary work computed and discarded.

Also notice Noah Kim is missing from the EXISTS result (correctly, he has zero orders), while Desk Lamp is the only product caught by NOT EXISTS (correctly, it is the one product ID that never appears in the orders table). Both results agree exactly with the order counts from Step 5, which is a good sign; when two different subquery techniques answering related questions agree with each other, that is a real, if informal, way to build confidence that both are correct.

Step 7: Two Subquery Mistakes That Bite Beginners

Both of the mistakes below are common precisely because they do not raise an error. They return an answer that looks plausible, and the only way to catch them is to know the specific situation that triggers each one.

Mistake 1: NOT IN Goes Quiet When the Subquery Contains a NULL

Reusing Step 6’s question, “which customers have never ordered anything,” here is a second, more intuitive-looking way to write it, using NOT IN instead of NOT EXISTS. Save this as step7_gotchas.py:

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()

print("customers who never ordered anything, the WRONG way (NOT IN):")
cur.execute("""
    SELECT name FROM customers c
    WHERE c.customer_id NOT IN (SELECT customer_id FROM orders);
""")
print("rows returned:", cur.fetchall())

print("\nwhat the subquery alone actually returns (note the None):")
cur.execute("SELECT DISTINCT customer_id FROM orders;")
print(cur.fetchall())

print("\ncustomers who never ordered anything, the RIGHT way (NOT EXISTS):")
cur.execute("""
    SELECT name FROM customers c
    WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
""")
print(cur.fetchall())

conn.close()

Real output:

customers who never ordered anything, the WRONG way (NOT IN):
rows returned: []

what the subquery alone actually returns (note the None):
[(1,), (2,), (3,), (4,), (5,), (None,)]

customers who never ordered anything, the RIGHT way (NOT EXISTS):
[('Noah Kim',)]

The NOT IN version returns an empty list. Not an error, not a warning, just zero rows, quietly wrong. Noah Kim genuinely has never placed an order, so the correct answer has exactly one row, which the NOT EXISTS version produces correctly.

Here is why NOT IN breaks. SQL uses three-valued logic: any comparison against NULL evaluates to neither true nor false but UNKNOWN, and a WHERE clause only keeps rows where the condition is true, silently dropping both false and UNKNOWN rows. The guest order from Step 1 means the subquery’s result set is {1, 2, 3, 4, 5, NULL}, and customer_id NOT IN (1, 2, 3, 4, 5, NULL) is really shorthand for customer_id <> 1 AND customer_id <> 2 AND ... AND customer_id <> NULL. That last comparison, anything compared to NULL, is always UNKNOWN, which poisons the whole chain of ANDs to UNKNOWN, for every single customer, regardless of their ID. The SQLite documentation spells this exact scenario out in its truth table for IN and NOT IN: when the left operand is not NULL, the right operand (here, the subquery) contains a NULL, the right operand is not an empty set, and the left operand is not found in the right operand, the table’s own columns “Result of IN operator” and “Result of NOT IN operator” both show NULL, not true. A NULL result is not a true result, so the row gets excluded from a WHERE clause exactly as if the condition had been false.

EXISTS sidesteps this completely, which is the second half of the SQLite documentation quoted in Step 6: EXISTS only checks whether any row comes back, and it explicitly does not treat NULL-containing rows any differently. The practical rule this leaves you with: prefer NOT EXISTS over NOT IN whenever the subquery’s column can contain NULL, which for a nullable foreign key like this one, is exactly the kind of column NOT IN tends to be used against.

Mistake 2: A Scalar Subquery That Secretly Returns More Than One Row

A scalar subquery, the kind used in Step 3, is only supposed to return one row. But nothing stops you from writing one that can return more than one, if the WHERE clause inside it is not as selective as you assumed. Save this as step7b_mistake2.py:

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()

print("how many customers ordered product 101 (Mechanical Keyboard)?")
cur.execute("SELECT customer_id FROM orders WHERE product_id = 101;")
print(cur.fetchall())

print("\nnow try to use it as a scalar subquery with '=':")
cur.execute("""
    SELECT name FROM customers
    WHERE customer_id = (SELECT customer_id FROM orders WHERE product_id = 101);
""")
print(cur.fetchall())

conn.close()

Real output:

how many customers ordered product 101 (Mechanical Keyboard)?
[(1,), (3,)]

now try to use it as a scalar subquery with '=':
[('Maria Chen',)]

Two customers ordered the Mechanical Keyboard: customer 1 (Maria Chen) and customer 3 (Priya Patel). The subquery SELECT customer_id FROM orders WHERE product_id = 101 therefore returns two rows, not one. Using it inside customer_id = (...) is exactly the situation a scalar subquery is not built for.

SQLite does not raise an error here. It silently picks one row and discards the rest. The official documentation for subquery expressions is explicit about which row it picks: “The value of a subquery expression is the first row of the result from the enclosed SELECT statement.” That is why the query above quietly returns only Maria Chen, dropping Priya Patel with no warning at all, even though both of them are equally correct answers to “who ordered product 101.”

This is arguably worse than an error, because the query runs successfully and returns a plausible-looking single name. Some other database engines are stricter about this. PostgreSQL’s own documentation on single-row comparisons states outright: “the subquery cannot return more than one row.” Where SQLite quietly truncates to the first row, PostgreSQL raises a runtime error the moment a scalar subquery like this one returns more than one row, which, while less forgiving, at least fails loudly instead of silently dropping data. Either way, the fix is the same: if a subquery is meant to be scalar, verify with a real query, not an assumption, that it can only ever return one row for every input you care about. Step 8 shows exactly how.

Step 8: Verify a Subquery Before You Trust It

Subqueries fail quietly, as Step 7 just demonstrated twice. Before you rely on one, especially inside application code where a wrong-but-plausible answer can go unnoticed for a long time, get in the habit of checking it two ways.

Run the Inner Query by Itself First

This is the single most useful debugging habit for subqueries, and it is the same technique used to build every example in this tutorial: before you nest a subquery inside a bigger query, run it on its own and read its result. Step 2 did this explicitly (“inner subquery alone”), and Step 7’s Mistake 2 did it too, running SELECT customer_id FROM orders WHERE product_id = 101 by itself and immediately seeing two rows come back, which is the exact moment you would catch that bug before it ever reached a scalar = comparison. If a subquery you intend to use as a scalar value or with IN returns something you did not expect (too many rows, a NULL you were not counting on, zero rows), you want to discover that in isolation, not as a confusing symptom three layers deep in a larger query.

Look at the Query Plan for Correlated Subqueries

Correlated subqueries are easy to write and easy to get right, but because SQLite reruns them once per outer row, as explained in Step 5, they can get slow on large tables in a way a non-correlated subquery never will. SQLite’s EXPLAIN QUERY PLAN command shows you, in plain text, whether a subquery is being treated as correlated. Save this as step8_explain.py:

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()

print("--- EXPLAIN QUERY PLAN: correlated subquery (Step 5) ---")
cur.execute("""
    EXPLAIN QUERY PLAN
    SELECT
        c.customer_id,
        c.name,
        (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
    FROM customers c;
""")
for row in cur.fetchall():
    print(row)

print("\n--- EXPLAIN QUERY PLAN: same result with a JOIN + GROUP BY instead ---")
cur.execute("""
    EXPLAIN QUERY PLAN
    SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.name;
""")
for row in cur.fetchall():
    print(row)

conn.close()

Real output:

--- EXPLAIN QUERY PLAN: correlated subquery (Step 5) ---
(2, 0, 216, 'SCAN c')
(7, 0, 0, 'CORRELATED SCALAR SUBQUERY 1')
(12, 7, 216, 'SCAN o')

--- EXPLAIN QUERY PLAN: same result with a JOIN + GROUP BY instead ---
(7, 0, 216, 'SCAN c')
(11, 0, 0, 'BLOOM FILTER ON o (customer_id=?)')
(20, 0, 53, 'SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN')

SQLite’s query planner labels the subquery plan step “CORRELATED SCALAR SUBQUERY 1” in plain text, with a nested “SCAN o” underneath it, which is the planner telling you outright: for every row scanned in “SCAN c” (the customers table), it will scan the orders table again to answer the subquery. On this six-row practice table, that difference is meaningless. On a customers table with a million rows, “scan the entire orders table, once per customer” is a very different cost profile than the JOIN version’s plan, which builds a Bloom filter and an automatic covering index on orders.customer_id, then does one indexed lookup per customer instead of a full rescan.

This is not a reason to avoid correlated subqueries; they are often the clearest way to express a question. It is a reason to check, with EXPLAIN QUERY PLAN, before you run one against a table large enough for the difference to matter. Confirm both versions actually agree on the answer, too:

SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY order_count DESC, c.customer_id;

(3, 'Priya Patel', 3)
(1, 'Maria Chen', 2)
(2, 'Devon Brooks', 1)
(4, 'Sam Okafor', 1)
(5, 'Lena Ivanova', 1)
(6, 'Noah Kim', 0)

That matches Step 5’s correlated subquery output exactly, row for row. Getting the same answer two different ways is a solid, practical check that both queries, and your understanding of what they are doing, are correct.

How to Confirm Everything Works

Run back through the full set in order and check the results against what is shown in this post:

1. python setup_db.py should print customers: 6, products: 6, orders: 9
2. step2_filter.py should return exactly 3 orders, all from customers 1 and 2
3. step3_derived_column.py should show an avg_price of 141.67 on every row
4. step4_derived_table.py should return exactly 3 customers, none below $150
5. step5_correlated.py should show Noah Kim with an order_count of 0
6. step6_exists.py should list 5 customers under EXISTS (everyone except Noah Kim) and exactly 1 product, Desk Lamp, under NOT EXISTS
7. step7_gotchas.py should show the NOT IN query returning an empty list while NOT EXISTS correctly finds Noah Kim, and step7b_mistake2.py should show the scalar subquery silently returning only Maria Chen despite two customers matching
8. step8_explain.py should label one plan step CORRELATED SCALAR SUBQUERY 1, and the JOIN-based rewrite from the same step should produce identical counts to Step 5

If every one of those checks out, you have not just copied working SQL, you have watched the exact failure modes this post warns about fail in front of you, and watched the fixes correct them.

Next Steps

Subqueries are one tool among several for this class of problem, not the only one. Two natural next things to learn:

• Common table expressions (the WITH clause) solve the “same subquery used twice” friction from Step 3 by letting you name a subquery once at the top of a query and reference that name as many times as you need, instead of repeating the SQL text
• Window functions (OVER (PARTITION BY ...)) can replace some correlated subquery patterns, like the per-customer order count in Step 5, with a single pass over the data instead of one subquery execution per row, which is often considerably faster on large tables

Both build directly on the mental models from this tutorial: knowing when a piece of SQL is evaluated once versus once per row is the same skill either way.

This tutorial was inspired by freeCodeCamp’s introduction to SQL subqueries, rebuilt here from scratch with an original dataset, and extended with the NOT IN plus NULL pitfall, the silent multi-row scalar subquery pitfall, and the EXPLAIN QUERY PLAN verification step. Technical claims about SQLite’s own behavior are drawn directly from the official SQLite SQL Language Expressions documentation, and the PostgreSQL comparison is drawn from the official PostgreSQL subquery documentation.