Semi-join

Keep the rows that have a match, each one at most once.

6 min read

Overview

Which customers have placed at least one order?

The question asks about customer membership rather than order detail. A semi-join tests whether a match exists without attaching order rows.

customers

id	name
1	Ana
2	Ben
3	Cara

orders

id	customer_id
101	1
102	1
103	2

Trace each customer id into orders. Which customers have at least one match, and how many rows should each contribute?

How it works

For each customer the database probes orders for a match on the key. The first match settles the question because one match proves existence, and the probe moves on to the next customer. Additional matches change nothing, so Ana’s second order never affects her row. No order row is attached at any point. That is why the result contains only customer columns.

select c.name
from customers c
where exists (
  select 1 from orders o where o.customer_id = c.id
)
order by c.id;

Run the demo and compare it with your prediction.

Exists, at most oncePress play, or step through it yourself.

customers c

id	name
1	Ana
2	Ben
3	Cara

orders o

id	customer_id
101	1
102	1
103	2

result

name

exists (o.customer_id = c.id)

Ana and Ben each appear once. Ana’s first matching order proves membership, so her second order cannot create another output row. Cara has no match and is filtered out.

The select 1 inside the probe is a convention rather than a requirement, because exists only asks whether any row comes back and ignores what the probe selects.

Patterns

The three patterns below are the same existence test applied to different tables.

1
Filter by activity
Customers who ordered, users who logged in this week, products with at least one review. Probe the activity table and a row qualifies on its first hit. The time or status condition goes inside the probe so it stays part of the existence test.
```
select c.name
from customers c
where exists (
  select 1 from orders o
  where o.customer_id = c.id
    and o.placed_at >= date '2026-06-01'
);
```
2
IN, the same idea by value
Where the key is a single column, where c.id in (select customer_id from orders) reads well and does the same job. Positive in is safe here. The negative form misbehaves around null, and the anti-join guide covers why.
3
Filter first, then aggregate
When a report should only cover active customers, semi-join first and aggregate after. The filter never multiplies rows and downstream aggregates stay correct.

Trade-offs

The tempting alternative is join plus distinct, which builds every customer-order pair and then collapses the duplicates. It produces the same list but does needless work to get there. The version is also fragile because an aggregate added before the collapse reads the inflated rows. Use a join when you need columns from both tables. Use the existence test when membership is the only question.

Semi and anti are one decision apart, since exists keeps rows with a match and not exists keeps rows without one. The rewrite is one word, but the direction of the question changes completely.

Pitfalls

JOIN plus DISTINCT hides a fan-out

The join-then-dedupe version usually looks equivalent. It stays equivalent until someone adds a count or a sum between the join and the distinct. The duplicates already sit in the rows and the aggregate inflates them, yet the query still completes without error. A semi-join never builds the duplicates, so this failure cannot occur. When distinct exists only to clean up a join, the query is usually answering the wrong question.

Correlate the probe. Without o.customer_id = c.id the probe asks whether any order exists at all, and every customer passes or none do.
At most once is the definition. If a customer appears twice in your result, the query has drifted into an ordinary join.
Keep the condition inside the probe. A recency or status test belongs in the exists subquery. Moved outside it filters a table that does not carry those columns, or forces the join you were avoiding.

Performance

A semi-join can stop logically after the first match, and extra matches do not create output rows. Check the probe key and ensure a row-producing join was not used accidentally.

Practice

EasyCompute city-level premium delivery-fee revenue with exists, keeping one row per order before aggregation. Reconcile the total so each qualifying order’s fee contributes once despite multiple premium items.MediumReturn recently reviewed products with the recency predicate inside an exists probe. Each qualifying product should appear once regardless of review count.HardReplace a join-plus-distinct filter with a semi-join and compare the pre-distinct row counts. The final parent set must stay unchanged without creating fan-out.

Recap

Reach for a semi-join when membership is the question and no columns from the probed table are needed.
Use a correlated exists probe and keep time or status conditions inside it.
Check that each kept row appears at most once regardless of how many matches exist.
Replace join-plus-distinct when the join only created duplicates that the query later removes.
Use an anti-join when absence qualifies the row, or an inner join when matched columns belong in the result.