Which customers have an order? You can ask that in T-SQL in three ways: with a JOIN, with WHERE EXISTS or with IN. For the reverse question, which customers have no order, you have LEFT JOIN ... IS NULL, NOT EXISTS and NOT IN. They seem to do the same thing. They do not always return the same result.
The short answer:
- Do you need columns from the other table? Use a
JOIN. - Do you only want to know whether a row exists? Use
EXISTS. - Do you want to know whether no row exists? Use
NOT EXISTS. - Do not use
NOT INon a column that can be NULL.
The test tables
I use two tables: 100,000 customers and 1,000,000 orders. The orders belong to 59,994 customers. A hundred orders have no customer: KlantId is NULL there. That happens in real life too, for example with a receipt without a loyalty card.
CREATE TABLE dbo.Klant( KlantId int NOT NULL PRIMARY KEY, Naam varchar(50) NOT NULL, Plaats varchar(50) NOT NULL);
CREATE TABLE dbo.Bestelling( BestellingId int NOT NULL PRIMARY KEY, KlantId int NULL, Besteldatum date NOT NULL, Bedrag decimal(10,2) NOT NULL);
-- 100.000 klantenWITH n AS ( SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b)INSERT dbo.Klant (KlantId, Naam, Plaats)SELECT i, CONCAT('Klant ', i), CASE i % 4 WHEN 0 THEN 'Utrecht' WHEN 1 THEN 'Zwolle' WHEN 2 THEN 'Breda' ELSE 'Leeuwarden' ENDFROM n;
-- 1.000.000 bestellingen van 60.000 klanten; 100 zonder klant (NULL)WITH n AS ( SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b)INSERT dbo.Bestelling (BestellingId, KlantId, Besteldatum, Bedrag)SELECT i, CASE WHEN i % 10000 = 0 THEN NULL ELSE 1 + (i * 7919) % 60000 END, DATEADD(DAY, i % 1000, '20240101'), i % 500 + 0.95FROM n;The script also works on older versions, because it does not use GENERATE_SERIES. I ran everything on SQL Server 2025 Developer Edition, first without and then with an index on Bestelling(KlantId). The reads below are logical reads from SET STATISTICS IO.
JOIN: when you need columns from the other table
SELECT k.KlantId, k.NaamFROM dbo.Klant AS kJOIN dbo.Bestelling AS b ON b.KlantId = k.KlantId;This query returns 999,900 rows. A JOIN makes a row for every match, so a customer with 17 orders appears 17 times. If you want to know which customers ordered something, that is wrong. If you want every order with the name of the customer, it is exactly right.
A JOIN is also the right choice when you add up something from the other table:
SELECT k.KlantId, k.Naam, SUM(b.Bedrag) AS OmzetFROM dbo.Klant AS kJOIN dbo.Bestelling AS b ON b.KlantId = k.KlantIdGROUP BY k.KlantId, k.Naam;Here you need b.Bedrag. With EXISTS you cannot reach it.
EXISTS: when you only want to know whether something is there
SELECT k.KlantId, k.NaamFROM dbo.Klant AS kWHERE EXISTS (SELECT 1 FROM dbo.Bestelling AS b WHERE b.KlantId = k.KlantId);This query returns 59,994 rows: each customer with an order, once. EXISTS is true as soon as the subquery finds a row. What is in the SELECT of the subquery does not matter. SELECT 1 is a habit.
Many people fix the duplicate rows of the JOIN with DISTINCT:
SELECT DISTINCT k.KlantId, k.NaamFROM dbo.Klant AS kJOIN dbo.Bestelling AS b ON b.KlantId = k.KlantId;That also returns 59,994 rows. So is EXISTS faster? Not in this test. SQL Server 2025 used the same plan for both queries, a Hash Match (Left Semi Join), with the same reads: 1,375 on Klant and 3,650 on Bestelling. IN got the same plan too. The optimizer sees that KlantId is the primary key and that you only ask for columns from Klant.
The difference is in what DISTINCT does. It does not prevent the duplicate rows. It removes them afterwards, and with them also rows that happen to be equal. Ask for the city alone:
-- 4 rijenSELECT DISTINCT k.PlaatsFROM dbo.Klant AS kJOIN dbo.Bestelling AS b ON b.KlantId = k.KlantId;
-- 59.994 rijenSELECT k.PlaatsFROM dbo.Klant AS kWHERE EXISTS (SELECT 1 FROM dbo.Bestelling AS b WHERE b.KlantId = k.KlantId);The first query returns 4 cities. The second returns 59,994 customers, with their city. Which one you want depends on your question. With EXISTS that question is in the query itself. With DISTINCT the reader has to guess whether it was a deliberate choice or a sticking plaster.
NOT EXISTS or LEFT JOIN … IS NULL
Now the reverse question: which customers never ordered anything?
SELECT k.KlantId, k.NaamFROM dbo.Klant AS kWHERE NOT EXISTS (SELECT 1 FROM dbo.Bestelling AS b WHERE b.KlantId = k.KlantId);
SELECT k.KlantId, k.NaamFROM dbo.Klant AS kLEFT JOIN dbo.Bestelling AS b ON b.KlantId = k.KlantIdWHERE b.KlantId IS NULL;Both return 40,006 customers. SQL Server turned both into a Hash Match (Left Anti Semi Join), with the same reads. With the index it became a Merge Join (Right Anti Semi Join) for both. So for speed it makes no difference here.
LEFT JOIN gives a wrong result more often. There are two traps.
A filter in the WHERE turns it into an inner join
Say you want all customers, with their 2026 orders if they have any:
-- 269.000 rijen: de klanten zonder bestelling in 2026 zijn wegSELECT k.KlantId, k.Naam, b.BestellingIdFROM dbo.Klant AS kLEFT JOIN dbo.Bestelling AS b ON b.KlantId = k.KlantIdWHERE b.Besteldatum >= '20260101';
-- 352.860 rijen: alle klanten, met of zonder bestelling in 2026SELECT k.KlantId, k.Naam, b.BestellingIdFROM dbo.Klant AS kLEFT JOIN dbo.Bestelling AS b ON b.KlantId = k.KlantId AND b.Besteldatum >= '20260101';For a customer without a match, b.Besteldatum is NULL, and NULL >= '20260101' is not true. So the WHERE throws those customers away. SQL Server sees that too: the plan of the first query shows Hash Match (Inner Join), not a Left Outer Join. An extra condition on the right table belongs in the ON.
With NOT EXISTS you do not have that problem. The condition sits in the subquery, where it belongs:
SELECT k.KlantId, k.NaamFROM dbo.Klant AS kWHERE NOT EXISTS (SELECT 1 FROM dbo.Bestelling AS b WHERE b.KlantId = k.KlantId AND b.Besteldatum >= '20260101');That returns 83,860 customers without an order in 2026. The LEFT JOIN with the date in the ON and WHERE b.BestellingId IS NULL returned the same number and the same plan.
Test IS NULL on the right column
Pick the join column or the primary key of the right table. It is never NULL when there is a match. If you test a column that may itself be NULL, you also count customers that do have an order, just with an empty value in that column.
I wrote earlier about calculating lost customers with T-SQL. That is the same question, per year.
NOT IN: the NULL trap
SELECT k.KlantId, k.NaamFROM dbo.Klant AS kWHERE k.KlantId NOT IN (SELECT b.KlantId FROM dbo.Bestelling AS b);This query returns 0 rows, while you expect 40,006. The cause is the hundred orders with KlantId NULL. NOT IN means: not equal to the first value, and not to the second, and so on. A comparison with NULL is neither true nor false, but unknown. Then the whole condition is unknown, and every customer drops out. Microsoft warns about it on the page about IN: “Using null values in together with IN or NOT IN can produce unexpected results.”
The plan is heavier too. SQL Server has to check separately whether the subquery contains a NULL. You see that as an extra Nested Loops with a Row Count Spool. The reads on Bestelling went from 3,650 to 4,058.
You can fix it with a filter in the subquery:
SELECT k.KlantId, k.NaamFROM dbo.Klant AS kWHERE k.KlantId NOT IN (SELECT b.KlantId FROM dbo.Bestelling AS b WHERE b.KlantId IS NOT NULL);That returns 40,006 rows again. But you have to remember it every time, even when the column holds no NULL today and has one next year. With NOT EXISTS you do not have to.
IN without NOT does not have this problem. In the documentation of EXISTS, Microsoft calls EXISTS and IN “semantically equivalent”, and in my test they got the same plan.
Look at the execution plan
Microsoft writes that the optimizer picks the best way to run a join itself, and that the execution “can’t be reliably predicted”. In my tests the optimizer rewrote JOIN with DISTINCT and LEFT JOIN ... IS NULL into the same semi join and anti semi join as EXISTS and NOT EXISTS. That is not a guarantee for every query you write.
When in doubt, open the actual execution plan. If it shows Left Semi Join or Anti Semi Join, SQL Server understood that it is only about existence. If it shows a Left Outer Join followed by a Filter, SQL Server first fetches all matches and then throws them away.
The index on Bestelling(KlantId) helped all variants equally. For EXISTS the reads on Bestelling went from 3,650 to 1,746, and on Klant from 1,375 to 276. It did not change the choice between JOIN, EXISTS and LEFT JOIN.
Summary
| Question | Pick | Why |
|---|---|---|
| Columns from both tables | JOIN |
You get a row per match, and that is what you want here. |
| Does the customer have an order? | EXISTS |
Each customer once, without DISTINCT. |
| Does the customer have no order? | NOT EXISTS |
Safe with NULL, and extra conditions sit in the right place. |
| All customers, with orders if there are any | LEFT JOIN |
Put conditions on the right table in the ON. |
| List of values without NULL | IN / NOT IN |
Only if the column is NOT NULL. |
In these tests the speed was equal for each pair. So pick the query that gives the result you want.
Sources
Frequently asked questions
What is the difference between JOIN and WHERE EXISTS in SQL Server?
A JOIN returns a row for every match, so a customer with 17 orders comes back 17 times. WHERE EXISTS returns each customer once, as soon as there is at least one order. Use JOIN when you need columns from the other table, and EXISTS when you only want to know whether something is there.
Is WHERE EXISTS faster than a JOIN with DISTINCT?
In the test with 100,000 customers and a million orders, SQL Server 2025 made the same plan for both, a Left Semi Join, with the same logical reads. The difference is in the result: DISTINCT also removes rows that happen to be equal, such as customers in the same city.
Why does NOT IN return no rows in SQL Server?
If the NOT IN subquery returns a single NULL, the comparison becomes UNKNOWN and every row drops out. In the test, NOT IN returned 0 rows because of 100 orders without a customer. NOT EXISTS returned the correct 40,006 customers.
Is LEFT JOIN with IS NULL the same as NOT EXISTS?
It usually gives the same result, and in the test SQL Server used the same Anti Semi Join plan for both. Watch two things: put extra conditions on the right table in the ON, not in the WHERE, and test IS NULL on the join column or the primary key.
Why does my LEFT JOIN turn into an INNER JOIN?
A filter on the right table in the WHERE throws away the rows without a match, because their columns are NULL. SQL Server then turns it into an Inner Join. In the test that gave 269,000 rows instead of 352,860. Put the filter in the ON.
Comments
Related blogs
SQL Server on-premises or Microsoft Fabric: what does the move really gain you?
Upgrading to SQL Server 2025: what you gain and what it costs
Microsoft MDS end of life: your options before 2028