BigQuery Subqueries and Nested Queries: 10 Code-Along Examples

The subqueries article explains the idea: a subquery is a query nested inside another, placed wherever a value or a table is expected. This workbook walks every place one can go, in the SELECT list, the FROM clause, and the WHERE clause, then covers IN versus EXISTS, correlated subqueries, nesting, and the CTEs that usually replace them. Run Example 1 first; the rest depend on it.

1. Create the sample tables

Run this once. Three small tables with tiny numbers so you can verify each result by eye. Note that Dan never buys anything and one purchase is refunded, which the later examples make use of.

-- Run once. Change `scratch` to any dataset you can write to.
CREATE OR REPLACE TABLE scratch.customers AS
SELECT * FROM UNNEST([
  STRUCT(1 AS customer_id, 'Ava'   AS name, 'UK' AS country),
  STRUCT(2, 'Ben',   'UK'),
  STRUCT(3, 'Chloe', 'US'),
  STRUCT(4, 'Dan',   'US')          -- never makes a purchase
]);
CREATE OR REPLACE TABLE scratch.products AS
SELECT * FROM UNNEST([
  STRUCT(101 AS product_id, 'Keyboard' AS name, 'Accessories' AS category, 80 AS price),
  STRUCT(102, 'Monitor', 'Displays', 300),
  STRUCT(103, 'Mouse', 'Accessories', 25),
  STRUCT(104, 'Webcam', 'Accessories', 60)
]);
CREATE OR REPLACE TABLE scratch.purchases AS
SELECT * FROM UNNEST([
  STRUCT(1 AS purchase_id, 1 AS customer_id, 102 AS product_id, 1 AS quantity, 300 AS amount, 'completed' AS status),
  STRUCT(2, 1, 101, 1, 80,  'completed'),
  STRUCT(3, 1, 103, 2, 50,  'refunded'),
  STRUCT(4, 2, 103, 1, 25,  'completed'),
  STRUCT(5, 3, 102, 4, 1200, 'completed'),
  STRUCT(6, 3, 101, 1, 80,  'completed')
]);
SELECT * FROM scratch.purchases ORDER BY purchase_id;

For reference: three of the four customers have bought something, and one of Ava’s purchases was later refunded.

2. A subquery in WHERE with IN

The most common subquery filters the outer rows against a list produced by an inner query. IN keeps rows whose value appears in that list. Here it finds customers who have made at least one purchase.

SELECT customer_id, name
FROM scratch.customers
WHERE customer_id IN (
  SELECT DISTINCT customer_id FROM scratch.purchases   -- the list of buyers
);

This returns Ava, Ben, and Chloe. The inner query produces a set of customer ids, and IN checks each outer row against it. Use IN, not =, whenever the subquery can return more than one row.

3. A subquery in WHERE with NOT IN

Flip to NOT IN to keep rows that are absent from the list, which finds customers who have never bought anything.

SELECT customer_id, name
FROM scratch.customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM scratch.purchases
);

Only Dan comes back. One important caution: NOT IN behaves unexpectedly if the inner query can return a NULL, because a comparison against NULL is never true. When the column might contain nulls, prefer NOT EXISTS, which handles the absence case cleanly. Here customer_id is never null, so NOT IN is safe.

4. A subquery in SELECT

A subquery in the SELECT list adds a computed column. It can be correlated, producing a value per row, or uncorrelated, producing the same value for every row. This query uses both at once.

SELECT
  c.name,
  (SELECT COUNT(*) FROM scratch.purchases p
   WHERE p.customer_id = c.customer_id) AS purchases,    -- per-row (correlated)
  (SELECT AVG(amount) FROM scratch.purchases) AS overall_avg  -- same for all rows
FROM scratch.customers AS c;

The purchases column changes per customer because it references c.customer_id, while overall_avg is a single global figure repeated on every row. Both are subqueries in SELECT, differing only in whether they depend on the outer row.

5. A subquery in FROM (a derived table)

A subquery in the FROM clause acts as a temporary table you can query further. This is how you aggregate first and then filter or join the result. The derived table must be given an alias.

SELECT spend.customer_id, spend.total
FROM (
  SELECT customer_id, SUM(amount) AS total
  FROM scratch.purchases
  GROUP BY customer_id
) AS spend                              -- the alias is required
WHERE spend.total > 100
ORDER BY spend.total DESC;

The inner query totals spend per customer, and the outer query keeps only the big spenders, Ava and Chloe. Forgetting the alias on a FROM subquery is one of the most common errors, and BigQuery will reject the query without it.

6. IN versus EXISTS

EXISTS is often a more efficient way to ask the same existence question as IN, because it stops as soon as it finds one match. Here are both, returning the same result.

-- IN: check the value against a produced list
SELECT name FROM scratch.customers AS c
WHERE c.customer_id IN (SELECT customer_id FROM scratch.purchases);
-- EXISTS: check whether any matching row exists
SELECT name FROM scratch.customers AS c
WHERE EXISTS (
  SELECT 1 FROM scratch.purchases AS p
  WHERE p.customer_id = c.customer_id
);

Both return the three buyers. EXISTS pairs with a correlated subquery and selects a literal 1, since it only cares whether a row exists, not what is in it. For existence checks on large tables, reach for EXISTS first.

7. A correlated subquery

A correlated subquery references a column from the outer query, so it runs once per outer row. This keeps each purchase that is above that customer’s own average purchase amount.

SELECT p.purchase_id, p.customer_id, p.amount
FROM scratch.purchases AS p
WHERE p.amount > (
  SELECT AVG(p2.amount)
  FROM scratch.purchases AS p2
  WHERE p2.customer_id = p.customer_id   -- correlates to the outer purchase
);

The same table appears twice under p and p2, so the inner average is scoped to the outer row’s customer. Unlike the uncorrelated subqueries above, this one cannot run on its own, because it depends on p.customer_id from the outer query.

8. A nested subquery, two levels deep

Subqueries can contain subqueries. This finds customers who bought the single most expensive product: the innermost query gets that product’s id, the middle query finds who bought it, and the outer query names them.

SELECT DISTINCT c.name
FROM scratch.customers AS c
WHERE c.customer_id IN (
  SELECT p.customer_id
  FROM scratch.purchases AS p
  WHERE p.product_id = (
    SELECT product_id FROM scratch.products      -- innermost: dearest product
    ORDER BY price DESC
    LIMIT 1
  )
);

The Monitor is the most expensive product, and its buyers are Ava and Chloe. Two levels is readable, but the guide warns against going deeper, because deeply nested subqueries quickly become hard to follow. That is exactly where CTEs come in.

9. Replacing a subquery with a CTE

A WITH clause defines a named subquery, called a CTE, at the top of the query. It does the same job as a FROM subquery but reads top to bottom and can be reused. Here is Example 5 rewritten.

WITH spend AS (
  SELECT customer_id, SUM(amount) AS total
  FROM scratch.purchases
  GROUP BY customer_id
)
SELECT c.name, spend.total
FROM scratch.customers AS c
JOIN spend ON spend.customer_id = c.customer_id
WHERE spend.total > 100;

The spend CTE is defined once and then used like a normal table. It produces the same big spenders as the derived-table version, but the logic is named and the query reads as a sequence of steps rather than a nest.

10. Chaining CTEs and filtering early

The real power of CTEs is chaining them into readable steps, filtering as early as possible so later steps scan less data. This totals only completed spend, excluding the refunded purchase, in two clean stages.

WITH completed AS (            -- step 1: filter early
  SELECT customer_id, amount
  FROM scratch.purchases
  WHERE status = 'completed'
),
spend AS (                     -- step 2: aggregate the filtered rows
  SELECT customer_id, SUM(amount) AS total
  FROM completed
  GROUP BY customer_id
)
SELECT c.name, spend.total
FROM scratch.customers AS c
JOIN spend ON spend.customer_id = c.customer_id
ORDER BY spend.total DESC;

Because the refund is filtered out in the first step, Ava’s completed total is 380 rather than 430, while Chloe stays at 1280. Filtering early matters in BigQuery because you are billed by bytes scanned, so cutting rows before the heavy work saves both time and money.

Work through these and you will have covered the whole article: subqueries in SELECT, FROM, and WHERE, the IN and EXISTS operators, correlated subqueries, nesting, and the CTEs that keep complex logic readable. The guide’s closing point is the one to remember: readable SQL and cheap SQL tend to be the same thing. Name your steps, filter early, and always alias a FROM subquery, and your queries will be easier to follow and cheaper to run.

See you soon.