Which is faster, a subquery or a JOIN?

Modern databases optimize most subqueries into something JOIN-like internally, so the difference is usually small. Choose for readability: a JOIN when you need columns from the other table, IN/EXISTS when you only filter by membership/existence. If in doubt, write both and look at the query plan.

Why does NOT IN sometimes return nothing?

If the list the subquery returns contains a NULL, NOT IN's logic turns to "unknown" and no row passes. The fix: use NOT EXISTS, or strip NULLs in the subquery (WHERE column IS NOT NULL).

Can I use a subquery in FROM (a derived table)?

Yes. You can use a subquery as a temporary table with FROM (SELECT ...) t; that is exactly what the top-N-per-group pattern does. For readability you can write the same thing as a CTE (WITH ...).

What if the subquery returns more than one row?

Where a scalar is expected (for example with =), a subquery returning multiple rows raises an error. In those cases use IN, or reduce the subquery to a single row (with MAX or LIMIT 1, say).

← All postsThe PerSight Team · Last updated: July 23, 2026 · 8 min read

SQL Subqueries: When, How, and the NOT IN Trap

A query inside a query: scalar subqueries, IN, EXISTS, and correlated subqueries. Why NOT IN returns nothing when NULLs are involved, and when to prefer a subquery over a JOIN.

A subquery is, as the name says, a query that runs inside another query. It earns its keep anywhere you first need to compute an intermediate result and then filter by it: "products priced above average," say, or "customers who never ordered." There are a few kinds and one famous trap; let's walk through them.

The scalar subquery: a query that returns one value

The simplest kind returns a single value (one row, one column), which you use in a comparison. Products above the average price:

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

The query in parentheses runs first and finds the average; the outer query compares each product against it. A scalar subquery can also sit in the SELECT list, for example to print the overall average next to each product.

IN and EXISTS: "is it in this list?"

A question like "customers who ordered this year" is really about membership in a list. There are two common ways: IN matches against a list of values, while EXISTS matches on the presence of a row:

-- With IN
SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE order_date >= DATE '2026-01-01'
);

-- With EXISTS (often more efficient)
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.order_date >= DATE '2026-01-01'
);

SQL Server does not accept the DATE literal; write the date as a plain string (2026-01-01) there.

EXISTS stops the moment it finds the first match and is usually faster on large tables. IN tends to read more easily. On small lists the difference is negligible; on big data sets, prefer EXISTS.

Watch out: the NOT IN and NULL trap

This one catches even experienced people. If the list the subquery returns contains a single NULL, NOT IN returns no rows at all. No error, just a silently empty result. The reason is that comparing against NULL yields "unknown." To find "customers who never ordered" without falling into this trap, use NOT EXISTS:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

Resistant to NULLs; prefer this over NOT IN.

What is a correlated subquery?

In the EXISTS examples above, the subquery refers to the outer row (c.id). That is a correlated subquery: it runs once per outer row. It is powerful, but if used carelessly it can be slow; in many cases you get the same result faster with a JOIN or a window function.

Frequently asked questions

Which is faster, a subquery or a JOIN?: Modern databases optimize most subqueries into something JOIN-like internally, so the difference is usually small. Choose for readability: a JOIN when you need columns from the other table, IN/EXISTS when you only filter by membership/existence. If in doubt, write both and look at the query plan.
Why does NOT IN sometimes return nothing?: If the list the subquery returns contains a NULL, NOT IN's logic turns to "unknown" and no row passes. The fix: use NOT EXISTS, or strip NULLs in the subquery (WHERE column IS NOT NULL).
Can I use a subquery in FROM (a derived table)?: Yes. You can use a subquery as a temporary table with FROM (SELECT ...) t; that is exactly what the top-N-per-group pattern does. For readability you can write the same thing as a CTE (WITH ...).
What if the subquery returns more than one row?: Where a scalar is expected (for example with =), a subquery returning multiple rows raises an error. In those cases use IN, or reduce the subquery to a single row (with MAX or LIMIT 1, say).

If you would rather not weigh IN against EXISTS, hand the question to PerSight as it is. Type "show customers who never ordered" and it picks the right pattern and explains, in plain language, the SQL it wrote.