Finding customers with orders does not require returning every matching order. Semi-joins express that membership question without multiplying the customer rows.
Start With the Required Result Grain
The business question asks for one row per customer who has at least one order. It does not ask for one row per customer-order pair. That result grain determines which query forms are directly equivalent.
EXISTS tests whether a qualifying inner row exists for each outer row. IN tests membership in the subquery's returned values. An ordinary inner JOIN creates matching row pairs instead.
I state the expected result grain before comparing query speed. I also put multiple matching inner rows into the test fixture. A one-order-per-customer sample can hide the duplication difference completely.
The temporary tables below include two orders for one customer and one unassigned order. The unassigned CustomerId is deliberately NULL. The customer table itself has a nonnullable primary key.
CREATE TABLE #Customer
(
CustomerId int NOT NULL PRIMARY KEY,
CustomerName nvarchar(40) NOT NULL
);
CREATE TABLE #CustomerOrder
(
OrderId int NOT NULL PRIMARY KEY,
CustomerId int NULL
);
CREATE INDEX IX_CustomerOrder_Customer ON #CustomerOrder(CustomerId);
INSERT #Customer VALUES (1,N'Alpha'),(2,N'Beta'),(3,N'Gamma');
INSERT #CustomerOrder VALUES (1,1),(2,1),(3,2),(4,NULL);Write Positive Semi-Joins With EXISTS and IN
EXISTS makes the customer-order relationship explicit in its correlated predicate. SELECT one inside EXISTS describes existence rather than a returned business value. The choice of that harmless constant does not request a count.
The IN form expresses the same positive membership question for these keys. Repeated CustomerId values inside the subquery do not multiply the outer row. Both forms express semi-joins and preserve the selected customer grain in this fixture.
SELECT C.CustomerId, C.CustomerName
FROM #Customer AS C
WHERE EXISTS
(
SELECT 1 FROM #CustomerOrder AS O
WHERE O.CustomerId = C.CustomerId
)
ORDER BY C.CustomerId;
SELECT C.CustomerId, C.CustomerName
FROM #Customer AS C
WHERE C.CustomerId IN
(
SELECT O.CustomerId FROM #CustomerOrder AS O
)
ORDER BY C.CustomerId;NULL in the positive IN subquery does not stop a genuine matching key from qualifying. An unmatched comparison can become UNKNOWN instead of FALSE. WHERE excludes both UNKNOWN and FALSE, leaving the same positive membership result here.
Avoid saying that NULL has no effect on IN in every expression. Boolean combinations and projected expressions can expose that distinction. Review the actual predicate context instead of repeating a simplified slogan.
See How an Ordinary JOIN Changes Membership
The following JOIN returns a row for every matching order. Customer one therefore participates in two selected pairs. That duplication is correct for a pair result but wrong for a one-row customer list.
DISTINCT can restore the requested customer projection in this example. It also introduces a duplicate-elimination requirement into the logical query. Use it because the result contract requires it, not to hide an unexplained join mistake.
SELECT C.CustomerId, C.CustomerName, O.OrderId
FROM #Customer AS C
JOIN #CustomerOrder AS O ON O.CustomerId = C.CustomerId
ORDER BY C.CustomerId, O.OrderId;
SELECT DISTINCT C.CustomerId, C.CustomerName
FROM #Customer AS C
JOIN #CustomerOrder AS O ON O.CustomerId = C.CustomerId
ORDER BY C.CustomerId;Returning an order identifier prevents DISTINCT from collapsing those pairs into one customer row. Every selected column contributes to duplicate elimination. Decide whether you need order details before calling that form equivalent to EXISTS.
The customer primary key also matters. A semi-join does not independently deduplicate duplicate outer rows. It preserves qualifying outer rows, so a malformed outer source can still produce duplicates.
Compare Plans for Semi-Joins Without Universal Claims
SQL Server can transform equivalent membership expressions into the same logical semi-join. The selected physical algorithm depends on estimates, indexes, and data distribution. Query spelling alone does not determine a universal winner.
Enable actual execution plans in SSMS for the equivalent membership queries. Compare logical operation, physical algorithm, estimated rows, actual rows, and reads. Keep the same projection and predicate when comparing their costs.
A nested-loops implementation can stop looking after establishing a match for an outer row. Other algorithms process the relationship differently. Do not promise that every EXISTS plan performs one short seek per customer.
The tiny fixture is useful for semantics rather than performance measurement. Its scans or join choices do not predict a large production workload. Use representative row counts and distributions before selecting a performance-driven rewrite.
In my run, EXISTS and IN each returned customers 1 and 2, while the plain JOIN returned three pairs. Run the scripts in one session, because the tables are temporary, and inspect the customer identifiers. The repeated inner key is an intentional input for revealing the result-grain difference.
Handle Exclusion and NULL Explicitly
NOT IN is the negative membership form, but a NULL inner value changes its behavior. A nonmatching key compared against that NULL yields UNKNOWN. WHERE does not accept UNKNOWN as a successful exclusion. In my run, the first query below returned no rows, and the other two returned customer 3.
NOT EXISTS tests whether any equality match exists instead. An unrelated NULL inner key does not match a nonnullable customer identifier. The unmatched customer can therefore qualify under the correlated exclusion query.
SELECT C.CustomerId, C.CustomerName
FROM #Customer AS C
WHERE C.CustomerId NOT IN
(
SELECT O.CustomerId FROM #CustomerOrder AS O
);
SELECT C.CustomerId, C.CustomerName
FROM #Customer AS C
WHERE NOT EXISTS
(
SELECT 1 FROM #CustomerOrder AS O
WHERE O.CustomerId = C.CustomerId
);
SELECT C.CustomerId, C.CustomerName
FROM #Customer AS C
WHERE C.CustomerId NOT IN
(
SELECT O.CustomerId FROM #CustomerOrder AS O
WHERE O.CustomerId IS NOT NULL
);The final NOT IN query removes inner NULL values deliberately. For this nonnullable outer key, it expresses the intended unmatched-customer result. That filtering decision belongs to the data contract rather than an automatic stylistic fix.
A nullable outer key creates another policy question. NOT EXISTS can accept it when no equality match exists. With a nonempty inner set, ordinary NOT IN comparisons involving that NULL remain UNKNOWN.
Decide whether unknown outer identifiers should qualify before choosing an exclusion pattern. Add an explicit IS NOT NULL condition when the business rule requires known identifiers. Neither form can infer that requirement from the column name.
Keep the Relationship Type Clear
A JOIN is appropriate when the result needs matching inner attributes. EXISTS is appropriate when the inner source only decides eligibility. IN remains a readable option for simple membership in one compatible key column.
Multi-column relationships are particularly clear with correlated EXISTS predicates. Specify every relevant key part rather than matching an incomplete identifier. A missing tenant key can create cross-tenant matches even when the join syntax is correct.
Compatible data types and collations also affect the chosen access path. Avoid converting the indexed inner key unnecessarily during comparison. Review parameter types and actual conversion warnings before blaming the membership form.
An index on the inner relationship key gives the optimizer useful access options. It does not guarantee a particular algorithm for every population. Include the full qualifying predicate when evaluating a supporting index.
Choose a Sensible Default for Semi-Joins
Do you need any columns from the matching orders in the output? If the answer is no, EXISTS usually expresses the intended eligibility directly. Use JOIN when the actual output grain requires matched records.
I default to EXISTS for relational membership and NOT EXISTS for relational exclusion. I still inspect the data contract and actual plan. A customer should not acquire extra seats simply because two orders came to dinner.
Use semi-joins as a statement of result meaning rather than a performance charm. Review outer uniqueness, inner NULLs, and composite keys explicitly. Those checks prevent more mistakes than a blanket rule about which keyword is fastest.
Retain a small fixture containing duplicates, unmatched keys, and NULL values beside important membership queries. Compare exact result identifiers when rewriting them. Equivalent performance is useful only after equivalent semantics are established.
Related reading on this blog: NOT IN With a NULL in the List Returns No Rows and Modern Explicit JOIN Syntax: A Brief Note.
A semi-join is not a shortened ordinary join, it is a membership operation that preserves the intended outer grain.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
