Subqueries and CTEs Updated November 2026

EXISTS

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) keeps customers that have at least one order; NOT EXISTS keeps those with none. The subquery is correlated to the outer row and stops at the first match, and unlike NOT IN it is not broken by NULLs.

Syntax

WHERE [NOT] EXISTS (SELECT 1 FROM other o WHERE o.key = outer.key)

Examples

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

Result: Customers with at least one order

What you select inside EXISTS does not matter; SELECT 1 is the convention.

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

Result: Customers who never ordered

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.amount > 500);

Result: Customers with at least one order above 500, each listed once

A JOIN would repeat the customer for every large order; EXISTS does not duplicate.

Common mistakes

Forgetting the correlation (WHERE o.customer_id = c.id) makes EXISTS true for every row as soon as the other table has any data.
Using NOT IN instead of NOT EXISTS with a subquery that can return NULL; NOT IN then returns nothing, NOT EXISTS behaves correctly.
Expecting EXISTS to return the matching rows from the inner table; it only filters the outer rows, so join if you need inner columns.

Practice EXISTS

These drills check your answer on live data, free, no account needed.

Customers who never orderedReturn the names of customers who have no orders at all.
Products never soldReturn the names of products that appear on no order line.

Questions

EXISTS vs IN: which is faster?

Modern optimizers often produce the same plan. EXISTS is preferable when the subquery is correlated or can return NULLs; IN reads naturally for a fixed list or a small lookup.

What is the difference between NOT EXISTS and LEFT JOIN ... IS NULL?

Both find rows without a match. NOT EXISTS states the intent directly and never duplicates rows; the LEFT JOIN form can be handy when you also want columns from the matched side.

INWHERE column IN (...) keeps rows whose value appears in a list or in a subquery result. Examples, NOT IN, the NULL trap, and when to use EXISTS instead.
SubqueryA subquery is a SELECT inside another query. Scalar subqueries (compare to an average), IN subqueries, correlated subqueries and derived tables in FROM.
LEFT JOINLEFT JOIN returns every row of the first table and matching rows from the second, or NULL. Find rows without a match, count with zeros, avoid the WHERE trap.
IS NULLNULL means unknown, so = NULL never matches. Use IS NULL and IS NOT NULL to find or exclude missing values, and see how NULL behaves in counts and sorting.