SQL Subqueries With Examples

Sometimes one query needs the result of another. A SQL subquery nests that inner query inside another SQL statement so its value or result set can filter, calculate, or select data.

This guide covers scalar and multirow subqueries, where to use them, IN versus EXISTS, correlated subqueries, and the NULL behavior that causes real bugs.

This article covers the "Subqueries" chapter of Learn SQL, where you can run each query against a real database.

What Is a Subquery in SQL?

Sometimes a single query is not enough to retrieve the specific records we need.

It is possible to run a query on the result set of another query – a query within a query! This is called "query-ception"... erm... I mean a "subquery."

Subqueries can be very useful in a number of situations when trying to retrieve specific data that wouldn't be accessible by simply querying a single table. A subquery is also called an inner query or nested query, and the statement containing it is the outer query.

Here is an example of a subquery:

SELECT id, song_name, artist_id
FROM songs
WHERE artist_id IN (
  SELECT id
  FROM artists
  WHERE artist_name LIKE 'Rick%'
);

In this hypothetical database, the query above selects all of the ids, song_names, and artist_ids from the songs table that are written by artists whose name starts with Rick. Notice that the subquery allows us to use information from a different table – in this case the artists table.

The only syntax unique to a subquery is the parentheses surrounding the nested query. The IN operator could be different; for example, we could use the = operator if we expect a single value to be returned. The PostgreSQL subquery documentation covers each comparison form.

Scalar vs. Multirow Subqueries

A scalar subquery returns exactly one column from at most one row. SQL can use that result anywhere it expects a single value.

When working on a back-end application, this doesn't come up often, but it's important to remember that SQL is a full programming language. We usually use it to interact with data stored in tables, but it's quite flexible and powerful. For example, you can SELECT information that's simply calculated, with no tables necessary.

SELECT 5 + 10 AS sum;
-- 15

That means a scalar subquery can be pure calculation. Suppose finance has found that people who have lived longer than 40 years need to start thinking about retirement, and they want all users who are more than 40 years old. Unfortunately, the table awkwardly stores age in days in the age_in_days field! A subquery can convert 40 years → days (assume every year has 365 days) and filter on that:

SELECT id, name, age_in_days
FROM users
WHERE age_in_days > (
  SELECT 40 * 365
);

The inner query returns one number, 14600, so the > comparison works.

A multirow subquery returns several rows. A normal = comparison can't compare one value against an entire set, so use the IN membership predicate.

Say you're building a payments app called CashPal. Certain customers have been using their personal CashPal accounts for business expenses. CashPal is trying to contact these customers so they can upsell business accounts. This query retrieves every user who matches the sender_id in a transaction with "invoice" or "tax" mentioned anywhere in the transaction note:

SELECT id, name
FROM users
WHERE id IN (
  SELECT sender_id
  FROM transactions
  WHERE note LIKE '%invoice%'
     OR note LIKE '%tax%'
);

PostgreSQL and many other databases raise an error if a scalar subquery returns more than one row. SQLite uses the first row instead, which can hide a broken uniqueness assumption. Make the inner query reliably return one row.

Where Subqueries Can Appear

Subqueries commonly appear in WHERE, FROM, and SELECT. Each placement does a different job.

Subqueries in WHERE

A subquery in WHERE supplies values for a filter. This is the most common form.

Back at CashPal, one of the customer service representatives needs us to pull all the transactions for a specific user. Trouble is, they only know the user's name, not their id:

SELECT *
FROM transactions
WHERE user_id = (
  SELECT id
  FROM users
  WHERE name = 'David'
);

This works only if the inner query returns at most one user ID. If names aren't unique, filter by a unique field or use IN.

Subqueries in FROM

A subquery in FROM acts like a temporary table for the rest of the query. Give it an alias:

SELECT customer_totals.customer_id, customer_totals.total
FROM (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS customer_totals
WHERE customer_totals.total > 500;

The inner query groups orders. The outer query filters those grouped results. PostgreSQL calls this kind of result a derived table.

Subqueries in SELECT

A scalar subquery in SELECT calculates a value for each output row:

SELECT
  users.id,
  users.name,
  (
    SELECT COUNT(*)
    FROM transactions
    WHERE transactions.user_id = users.id
  ) AS transaction_count
FROM users;

This example is correlated: the inner query refers to the current users row. For large result sets, a join with grouping may be clearer and faster.

IN vs. EXISTS

IN asks whether a value matches any value returned by the subquery:

SELECT id, name
FROM users
WHERE id IN (
  SELECT sender_id
  FROM transactions
  WHERE note LIKE '%invoice%'
);

EXISTS asks whether the subquery returns at least one row:

SELECT id, name
FROM users
WHERE EXISTS (
  SELECT 1
  FROM transactions
  WHERE transactions.sender_id = users.id
    AND transactions.note LIKE '%invoice%'
);

Use IN to compare a value against a set. Use EXISTS when you only care whether a related row exists. Database optimizers often produce similar plans for both, so EXISTS isn't always faster. Check the query plan against your database and data.

NOT EXISTS is usually the cleanest way to ask for rows with no related match. It's also safer than NOT IN when nullable data is involved.

Correlated Subqueries Explained

A correlated subquery references a column from the outer query. It can't run independently because its result depends on the current outer row:

SELECT id, name
FROM users
WHERE EXISTS (
  SELECT 1
  FROM transactions
  WHERE transactions.sender_id = users.id
    AND transactions.amount > 100
);

For each user, the inner query checks for a transaction from that user's id over 100. Qualify columns with table names or aliases. Ambiguous column names in nested queries are an avoidable mess.

Correlated subqueries are often readable, especially with EXISTS, but they can do more work than uncorrelated subqueries. Use EXPLAIN instead of guessing about performance, and check whether an index supports the correlated lookup.

How Does NULL Affect Subqueries?

SQL uses three-valued logic: a comparison can be TRUE, FALSE, or UNKNOWN. NULL comparisons produce UNKNOWN, and WHERE keeps only rows for which the condition is TRUE.

That makes NOT IN dangerous when its subquery can return NULL:

SELECT id, name
FROM users
WHERE id NOT IN (
  SELECT sender_id
  FROM transactions
);

If even one sender_id is NULL, the predicate evaluates to UNKNOWN for users without a matching non-NULL ID, so the query drops the rows you expected it to return. PostgreSQL documents this behavior explicitly for NOT IN.

Filter out NULL values:

SELECT id, name
FROM users
WHERE id NOT IN (
  SELECT sender_id
  FROM transactions
  WHERE sender_id IS NOT NULL
);

Or use NOT EXISTS, which states the intent directly:

SELECT id, name
FROM users
WHERE NOT EXISTS (
  SELECT 1
  FROM transactions
  WHERE transactions.sender_id = users.id
);

Don't use = NULL or != NULL. Use IS NULL and IS NOT NULL.

Subqueries vs. Joins vs. CTEs

Subqueries, joins, and common table expressions overlap, but each fits a different shape of query.

A join is often clearer when you need columns from both tables. A subquery is often clearer for membership or existence checks. A common table expression, written with WITH, can name stages in a longer query.

Don't rewrite a readable subquery because somebody told you joins are always faster. Modern optimizers transform many equivalent queries into similar plans. Pick the clearest correct form, then measure if the query is slow.