An anti-join returns rows from one input that have no qualifying match in another, commonly expressed with NOT EXISTS or LEFT JOIN and a missing-key test.
Customers who never purchased and sales with invalid customer links are both unmatched-record questions, but their starting populations differ. Define which side’s missing relationship matters before choosing the anti-join direction.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How Anti-joins and unmatched records works
A customer anti-join starts from customers and excludes any customer for whom a matching sale exists. NOT EXISTS directly expresses that absence. It returns Dev in this dataset, while Ana, Ben and Cara have evidence of purchases. The existence of an unrelated anonymous sale does not prevent Dev from qualifying because the correlation tests only sales with Dev’s customer id.
The outer-join form starts with customers LEFT JOIN sales ON the relationship and keeps WHERE sales.id IS NULL. The right-side key must prove that a real matching row was absent. Testing sales.amount IS NULL is wrong here because Cara has an actual sale with a missing amount. A real sales.id is never NULL, so a NULL id in the join result signals the placeholder rather than a nullable business field.
Unmatched rows with extra conditions
The eligible match may include extra conditions. Customers without a paid sale are tested against sales with paid status inside the NOT EXISTS subquery or ON clause. They are not the same population as customers without any sale. A customer with pending-only purchases belongs to the first population but not the second. Put the qualifying rule where it defines matches, and keep customer population restrictions separate.
NOT IN has different NULL semantics and is not a careless substitute. The sales customer_id set includes NULL, so a nonmatching customer comparison can become unknown and disappear from WHERE. NOT EXISTS avoids that membership trap for relationship absence. For a legacy-data audit of invalid sale references, start from sales, require customer_id IS NOT NULL, and look for no corresponding customer. The anonymous sale is absent ownership rather than an invalid non-NULL key; keep these categories distinct.
MySQL, PostgreSQL, SQL Server and SQLite
Both NOT EXISTS and LEFT JOIN ... IS NULL are supported by all four systems. Optimisers may recognise an anti-join and choose efficient physical operations, but neither written form is universally faster. Nullable join keys and additional match conditions must be reviewed before treating the forms as interchangeable.
Check the engine-specific syntax and version requirements in PostgreSQL EXISTS semantics.
Practice task and worked solution
Use NOT EXISTS to reproduce the customer-without-sales result from the left-join example.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
SELECT c.id,c.name FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM sales AS s WHERE s.customer_id=c.id)
ORDER BY c.id;id | name
4 | DevThe subquery searches for evidence belonging to each customer. There is no qualifying sale for Dev, so NOT EXISTS is true. It does not need to inspect a nullable amount and is unaffected by the anonymous customer_id in an unrelated sale.
Replace the missing-key condition with s.amount IS NULL and observe that Cara’s incomplete sale enters alongside Dev. Restore s.id IS NULL to recover true no-sale customers. Add a paid filter inside NOT EXISTS to change the evidence being tested. In a disposable copy with pending-only activity, verify that its customer lacks paid evidence but does not lack all sales.
SQL Anti-Joins: Find Unmatched Rows with NOT EXISTS Syntax
Anti-joins and unmatched records
SELECT c.id,c.name FROM customers AS c
LEFT JOIN sales AS s ON s.customer_id=c.id
WHERE s.id IS NULL ORDER BY c.id;SQL Anti-Joins: Find Unmatched Rows with NOT EXISTS Summary
- An anti-join returns rows from one table that have no qualifying match in another.
- Write it as
NOT EXISTS (correlated subquery)or asLEFT JOIN ... WHERE right_key IS NULL. - Test a right-side column that is always present for real matches, normally its primary key.
- Put extra match rules, such as status or period, inside the subquery or the
ONclause. - Avoid
NOT INwhen the subquery can returnNULL, because it then returns no rows at all.
SQL Anti-Joins: Find Unmatched Rows with NOT EXISTS Examples
Each example shows the code, the output it produces and what each part does.
Customers without sales using LEFT JOIN
The left join keeps every customer, and the missing-key test removes all combinations with a real sale. Dev alone has a right-side placeholder. Cara’s unknown amount is irrelevant because both of her sale ids are present.
SELECT c.id,c.name FROM customers AS c
LEFT JOIN sales AS s ON s.customer_id=c.id
WHERE s.id IS NULL ORDER BY c.id;id | name
4 | DevCommon SQL Anti-Joins: Find Unmatched Rows with NOT EXISTS Mistakes
Testing a nullable column for no match
WHERE s.amount IS NULL returns Cara’s unknown-amount sale as well as Dev’s placeholder, mixing incomplete data with no relationship. Use a guaranteed-present right-side key for the unmatched test. Also avoid NOT IN when its subquery can include NULL without an intentional NULL policy.
SQL Anti-Joins: Find Unmatched Rows with NOT EXISTS in Real Projects
Inactive accounts for a customer success team
A customer success team can identify accounts without qualifying activity in a chosen period. Define the period and activity status inside the match test, then preserve the intended customer population. An absence report should state whether it means no records ever, no paid records, or no recent records.
Keep absence reports specific about eligible evidence. No transaction, no paid transaction and no transaction in a period are different customer populations. For legacy integrity checks, distinguish a NULL optional reference from an invalid non-NULL parent id. Use the parent identity for matching and a guaranteed-present matched key for the outer-join absence test.
SQL Anti-Joins: Find Unmatched Rows with NOT EXISTS FAQ
Is anti-join a required SQL keyword?
No. It names a relationship operation, "rows with no match", commonly expressed through NOT EXISTS or an outer join. Some databases show "anti join" in their execution plans when they recognise the pattern.
Which side should I start from?
Start from the entities whose missing matches you want to list: customers for "customers with no sales", or sales for "sales whose customer doesn't exist". The two questions return completely different rows.
Why exclude NULL references from an invalid-link audit?
A deliberately absent optional relationship, such as the anonymous sale with no customer_id, is a different category from a non-NULL key pointing to no parent. Report them separately: one may be normal, the other is a data integrity problem.
Is NOT EXISTS faster than LEFT JOIN ... IS NULL?
Neither is universally faster. Modern optimisers often run both as the same anti-join, so choose the form that reads most clearly; many developers prefer NOT EXISTS because it states the intent and can't multiply rows.
How do I find orphaned rows whose parent was deleted?
Start from the child table: SELECT s.* FROM sales s WHERE s.customer_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = s.customer_id). A foreign key constraint prevents such rows from appearing in the first place.
SQL Anti-Joins: Find Unmatched Rows with NOT EXISTS Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which customer is incorrectly added if the anti-join tests s.amount IS NULL instead of s.id IS NULL?
SQL Anti-Joins: Find Unmatched Rows with NOT EXISTS Quiz
2 questions to check what you learned.
Which right-side column safely detects no sale here?
- sales.amount, which is missing only for nonexistent sales
- sales.id
- customers.id, which is present for every outer customer
Why can NOT IN fail for this relationship question?
- NOT IN automatically duplicates rows when the set repeats an id
- NOT IN compares only the first returned customer id
- The returned customer_id set includes NULL