BigQuery Subqueries and Nested Queries

Once your SQL grows past a single SELECT, subqueries are what keep it manageable. They let you break a hard problem into smaller pieces, reuse logic instead of repeating it, and write queries that read top to bottom like a sentence rather than a knot. A subquery is simply a query placed inside another query, and you can drop one into almost anywhere a value or a table is expected: the SELECT list, the FROM clause, the WHERE clause, or a WITH block. The terms can get loose in conversation, so it helps to pin them down. A subquery is the general idea, a query nested inside another. “Nested query” is usually just another name for the same thing. A correlated subquery is the special case where the inner query depends on the outer one, referring back to its columns row by row. This article walks through each placement in BigQuery with worked examples, using a simple schema of a customers table and a purchases table.

A Subquery in the SELECT Clause

When you want to compute a value for each row inline, you can put a subquery directly in the SELECT list. Here, each customer is returned alongside a live count of their purchases.

SELECT
  customer_id,
  (SELECT COUNT(*)
   FROM `analytics-warehouse.shop.purchases` p
   WHERE p.customer_id = c.customer_id) AS purchase_count
FROM `analytics-warehouse.shop.customers` c

The inner query runs once conceptually for each outer row, counting that customer’s purchases. This is convenient and readable for a single derived column, though for many such columns a join is usually cheaper, because each inline subquery scans the purchases table again.

A Subquery in the FROM Clause

A subquery in the FROM clause acts as a temporary inline table, often called a derived table. It is the natural way to filter or reshape data before the main query operates on it.

SELECT product_id, AVG(purchase_amount) AS avg_purchase
FROM (
  SELECT product_id, purchase_amount
  FROM `analytics-warehouse.shop.purchases`
  WHERE status = 'completed'
)
GROUP BY product_id

The inner query keeps only completed purchases, and the outer query then averages the amount per product. One BigQuery rule to remember: a subquery in the FROM clause should be given an alias, and many SQL dialects require it, so it is a good habit to write ... ) AS completed even when BigQuery would tolerate its absence.

A Subquery in the WHERE Clause

In the WHERE clause, a subquery filters the outer rows by checking them against a set or a condition. This returns every customer who has at least one cancelled purchase.

SELECT customer_id, name
FROM `analytics-warehouse.shop.customers`
WHERE customer_id IN (
  SELECT DISTINCT customer_id
  FROM `analytics-warehouse.shop.purchases`
  WHERE status = 'cancelled'
)

The inner query produces the list of customer IDs with a cancelled purchase, and IN keeps only the outer customers whose ID appears in that list. Using IN rather than = matters here, because the subquery returns many rows, and = would only work against a single value.

Filtering With EXISTS

EXISTS is often a cleaner and faster way to express “does at least one matching row exist.” Instead of building a full list of IDs, it stops as soon as it finds one match.

SELECT c.customer_id
FROM `analytics-warehouse.shop.customers` c
WHERE EXISTS (
  SELECT 1
  FROM `analytics-warehouse.shop.purchases` p
  WHERE p.customer_id = c.customer_id AND p.status = 'pending'
)

This returns every customer with at least one pending purchase. The SELECT 1 is a convention: EXISTS only cares whether any row comes back, not what it contains, so there is no point selecting real columns.

Correlated Subqueries

The EXISTS example above is also a correlated subquery, because the inner query refers to c.customer_id from the outer query. That reference is what makes it correlated: the inner query cannot run on its own, since it needs a value from each outer row to evaluate against.

SELECT customer_id, name
FROM `analytics-warehouse.shop.customers` c
WHERE EXISTS (
  SELECT 1
  FROM `analytics-warehouse.shop.purchases` p
  WHERE p.customer_id = c.customer_id AND p.purchase_total > 100
)

Here every customer row is checked against its own purchases to see whether any exceeded 100. Correlated subqueries are expressive, but they are the ones to watch for performance, since conceptually the inner query is evaluated per outer row.

Common Table Expressions: The WITH Clause

For anything beyond a quick one-off, a Common Table Expression is the most readable choice. The WITH clause lets you define a named, reusable subquery at the top of the statement and then refer to it by name below.

WITH top_customers AS (
  SELECT customer_id, SUM(purchase_total) AS total_spent
  FROM `analytics-warehouse.shop.purchases`
  GROUP BY customer_id
)
SELECT *
FROM top_customers
WHERE total_spent > 1000

This computes each customer’s total spend once, names that result top_customers, and then filters it. The benefit is partly readability, the logic reads in the order you think about it, and partly reuse, since you can reference the same CTE multiple times in one query instead of repeating the subquery. For complex analysis, chaining several CTEs is far easier to follow and debug than burying the same logic in nested parentheses.

Nesting Subqueries

You can also nest subqueries several layers deep, with each layer feeding the one above it.

SELECT *
FROM (
  SELECT customer_id, COUNT(*) AS purchase_count
  FROM (
    SELECT customer_id
    FROM `analytics-warehouse.shop.purchases`
    WHERE status = 'completed'
  )
  GROUP BY customer_id
)
WHERE purchase_count > 3

Reading inside out: the innermost query keeps completed purchases, the middle layer counts them per customer, and the outer layer keeps only customers with more than three. This works, but it is also a good illustration of why deep nesting is worth avoiding. The exact same logic expressed as two named CTEs would be far easier to read and to debug, which is the practical argument for reaching for WITH once you pass a single level of nesting.

Best Practices and Common Pitfalls

A few habits keep subqueries clean and cheap. Prefer WITH clauses for anything non-trivial, because named steps are easier to read, reuse, and debug than stacked parentheses. Always alias a subquery used in the FROM clause, since this is a frequent source of errors. Avoid deeply nested subqueries when a couple of CTEs would express the same thing more clearly. And remember that every subquery still scans data, so in BigQuery, where you pay by bytes scanned, filter early inside the inner queries rather than dragging full tables up through every layer.

The pitfalls follow from those same points. A subquery in FROM without an alias throws an error, so add AS name. A subquery used with = that returns more than one row will fail, so use IN (or EXISTS) when the inner query can produce multiple rows. And poor performance usually traces back to scanning more than you need, so push your filters down into the innermost query and strip out unnecessary layers.

Choosing the Right Form

The placement you reach for follows the job. Use a SELECT subquery for a single inline per-row value, though a join often scales better. Use a FROM subquery to filter or aggregate before the main query runs. Use a WHERE subquery, with IN or EXISTS, to keep or drop outer rows based on a condition in another table. Use a correlated subquery with EXISTSwhen the check depends on each outer row individually. And use a WITH clause whenever the logic is reused or complex enough that a name makes it clearer.

Conclusion

Subqueries turn one big, tangled query into a set of smaller, comprehensible steps. Put one in SELECT for an inline value, in FROM to reshape data before you use it, and in WHERE with IN or EXISTS to filter against another table, reaching for a correlated subquery when the test depends on each outer row. Once the logic gets reused or more than one level deep, lift it into a WITH clause, because in BigQuery, readable SQL and cheap SQL tend to be the same SQL: clear named steps that filter early and scan only what they need.