Missing rows can tell you where data is absent, not broken. A store can have customers who haven’t placed an order yet, payroll data can include employees with no department row, and a catalog can list products that don’t have an inventory record. SQL handles those cases with anti join logic, where rows from one table stay in the result only if the matching row from the other table is absent. The three common forms are NOT EXISTS, LEFT JOIN, and EXCEPT. NOT EXISTS checks that a related subquery returns no rows. LEFT JOIN keeps the left-side row and fills right-side columns with NULL when no match is found. EXCEPT compares two result sets and returns rows from the first query that are missing from the second. PostgreSQL, SQL Server, MySQL, and Oracle all support the pieces needed for these anti join queries, with Oracle treating EXCEPT as a synonym for MINUS and MySQL supporting EXCEPT in current versions.
What an Anti Join Does
Missing-match queries start with a practical question. Which rows on the left side have no related row on the right side. SQL engines can express that idea through different forms, but the relational logic stays the same. The left side supplies the rows you care about, while the right side acts as a match test. If the test finds a related row, the left row does not belong in the missing-match result. If the test finds nothing, the left row stays.
The Question It Answers
Relational tables usually store connected facts across separate places. The customers table can store customer identity, while the orders table stores purchases. Someone can be a customer before placing an order, so the absence of an order row can carry useful meaning. Anti join logic gives you the rows where that relationship has not happened yet.
SQL Server execution plans can represent this logical operation as a left anti semi join. The name has three useful parts. Left tells you the result comes from the first input. Anti tells you matching rows are rejected rather than kept. Semi tells you the right side is checked for existence, but its columns are not added to the final row. MySQL can use the same anti join idea internally for some negated subqueries, where the optimizer treats certain missing-match checks as an antijoin.
Customer and order data gives the relationship something specific to follow:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL
);
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alex'),
(2, 'Kaitlyn'),
(3, 'Pippin'),
(4, 'Morgan');
INSERT INTO orders (order_id, customer_id, order_date) VALUES
(101, 1, DATE '2026-05-01'),
(102, 1, DATE '2026-05-03'),
(103, 3, DATE '2026-05-04');Customer 1 has two matching order rows, and customer 3 has one matching order row. Customers 2 and 4 have no matching order row at all. The missing-match result should contain only Kaitlyn and Morgan, because those are the left-side rows with no related row in orders.
Looking first at the positive relationship can make the missing side easier to read:
SELECT
c.customer_id,
c.customer_name,
o.order_id
FROM customers c
JOIN orders o
ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;That join result would contain Alex twice and Pippin once, because matching order rows exist for those customers. Kaitlyn and Morgan would not appear, because an inner join keeps matched pairs only. Anti join logic asks for the opposite side of that relationship. It wants the customer rows that did not produce any joined pair.
The right-side table usually does not contribute values to an anti join result. It acts more like a gate than a source of displayed data. The query is not trying to display order details. It is trying to find customers whose related order details are absent. That distinction explains why anti join logic appears in audit queries, cleanup reports, data quality checks, and workflow screens that need to find records still waiting for a related action.
Rules That Change the Result
Duplicate rows can affect the amount of data the database checks, but they do not change the basic anti join answer when the rule is based on existence. In the sample data, Alex has two orders. That does not make Alex more matched than Pippin for missing-row logic. Both customers have at least one related row, so both are rejected by an anti join that looks for customers with no orders.
The number of right-side rows becomes visible only when the query asks for it directly. The next query counts the matches for every customer, including customers with none:
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;That result would show 2 for Alex, 1 for Pippin, and 0 for Kaitlyn and Morgan. The anti join question does not care about the difference between one match and two matches. It only cares about zero versus at least one, which is why missing-match logic is best read as an existence test instead of a counting test.
NULL values need careful reading, because SQL treats them differently depending on the operation. In a left outer join, unmatched right-side columns come back as NULL. In PostgreSQL, a LEFT OUTER JOIN keeps the left-side row if no right-side match exists, then fills the columns from the right table with NULL. That null-filled row is not a stored order row. It is the outer join result carrying the left row forward after the match failed.
The outer join output makes that behavior easier to see:
SELECT
c.customer_id,
c.customer_name,
o.order_id AS matched_order_id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;For Kaitlyn and Morgan, matched_order_id would be NULL because the join did not find a row in orders. That NULL comes from the join result, not from a stored order id. The distinction is important when reading missing-match output, because a NULL shown after an outer join can mean no row was found on the right side.
Set difference has its own rules as well, for example EXCEPT compares rows returned by two queries. PostgreSQL treats EXCEPT as distinct by default, with EXCEPT ALL available for duplicate-preserving set difference. SQL Server’s EXCEPT returns distinct rows from the left input that do not appear in the right input. That means a set difference query can collapse duplicate projected rows before returning the result.
The next pair of tables separates duplicate handling from the customer-order example:
CREATE TABLE planned_customer_ids (
customer_id INT
);
CREATE TABLE billed_customer_ids (
customer_id INT
);
INSERT INTO planned_customer_ids (customer_id) VALUES
(2),
(2),
(4),
(5);
INSERT INTO billed_customer_ids (customer_id) VALUES
(5);If set difference is run from planned_customer_ids to billed_customer_ids, the distinct result would include 2 and 4. Customer id 5 is removed because it appears in both result sets. The repeated 2 values do not produce two output rows unless the database engine supports the ALL form and that form is chosen.
NULL has a separate rule inside set operators. For EXCEPT and INTERSECT distinct-row checks in SQL Server, two NULL values are treated as equal. That differs from a normal comparison predicate, where NULL = NULL does not evaluate as true. If the projected row contains nullable columns, this can change which rows survive the set comparison.
Nullable projected values can be tested with a small account-code table:
CREATE TABLE expected_codes (
account_id INT,
region_code VARCHAR(10)
);
CREATE TABLE active_codes (
account_id INT,
region_code VARCHAR(10)
);
INSERT INTO expected_codes (account_id, region_code) VALUES
(10, NULL),
(11, 'WI'),
(12, NULL);
INSERT INTO active_codes (account_id, region_code) VALUES
(10, NULL);Set comparison would treat the (10, NULL) row as present on both sides for distinct-row matching. The row for account 10 would be removed from the set difference, while accounts 11 and 12 would remain if they have no matching projected row on the right. That rule is worth keeping in mind when the missing-match check projects nullable columns instead of stable non-null identifiers.
Three Ways to Write It
SQL gives you several ways to express an anti join, and the best form depends on how the missing match is being tested. Some queries read like a direct absence check, some read like an outer join followed by a null filter, and some read like set subtraction. All three can answer the same business question, but each form leads the reader through the logic from a different angle.
NOT EXISTS
Correlated NOT EXISTS says that a related row should not be present. The outer query chooses a row from the left table, then the subquery checks the right table for a match tied to that outer row. If the subquery finds at least one row, the left row is rejected. If the subquery finds no rows, the left row remains in the result.
The subquery does not need to return meaningful data to the final output. It only needs to test existence, which is why SELECT 1 commonly appears inside the subquery. The value 1 is not displayed later. It is just a compact way to say the subquery found a row.
This query finds employees who have not recorded any badge swipe:
SELECT
e.employee_id,
e.employee_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM badge_swipes b
WHERE b.employee_id = e.employee_id
);The relationship inside the subquery does the main comparison. b.employee_id = e.employee_id ties the right-side check back to the current employee from the outer query. Without that connection, the query would not be checking swipes for each employee. It would be checking the right table as a whole, which asks a very different question.
NOT EXISTS also handles extra matching rules without changing the meaning of the outer result. If the question is about employees who have not swiped into the building today, the date rule belongs inside the subquery because it defines which badge swipes count as a match.
SELECT
e.employee_id,
e.employee_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM badge_swipes b
WHERE b.employee_id = e.employee_id
AND b.swipe_time >= TIMESTAMP '2026-05-18 00:00:00'
AND b.swipe_time < TIMESTAMP '2026-05-19 00:00:00'
);That version does not ask if an employee has ever swiped a badge. It asks if the employee has no badge swipe in the chosen time window. Past swipes outside that range do not block the employee from appearing, because they are not part of the match rule.
Composite matches follow the same idea. Some relationships need more than one column to identify a valid match. A support system, for instance, could require both ticket_id and customer_id to match before an update counts as related to the ticket.
SELECT
t.ticket_id,
t.customer_id,
t.subject
FROM support_tickets t
WHERE NOT EXISTS (
SELECT 1
FROM ticket_updates u
WHERE u.ticket_id = t.ticket_id
AND u.customer_id = t.customer_id
);That query keeps tickets with no matching update for the same ticket and customer pair. The outer row stays only when the right table fails to produce a related row under the full match rule.
NOT EXISTS also avoids a common null issue tied to NOT IN. If the subquery behind NOT IN can return NULL, SQL’s three-valued logic can produce results that surprise readers. NOT EXISTS checks for row presence instead, so it is a safer fit for anti join logic when nullable values can appear on the right side.
LEFT JOIN With a Null Check
Outer join syntax reaches missing matches by keeping the left-side rows first, then checking which ones failed to match on the right side. A LEFT JOIN returns every row from the left table. When no row from the right table matches, the right-side columns in the joined result are filled with NULL. The anti join part happens when the query filters for those null-filled right-side values.
This query finds products that do not have an inventory row:
SELECT
p.product_id,
p.product_name
FROM products p
LEFT JOIN inventory i
ON i.product_id = p.product_id
WHERE i.inventory_id IS NULL;The ON clause defines what counts as a match, and the WHERE clause then keeps only the rows where the right-side row did not exist. For the null check, the safest target is a right-side column that cannot be null when a row exists. In this case, i.inventory_id works well if it is the primary key of inventory, because a matched inventory row should always have that value.
Filter placement needs care with this form. Conditions that define a valid match belong in the ON clause, while the final WHERE clause should contain the null check that identifies the missing right-side row.
Here, the query finds products with no active inventory row:
SELECT
p.product_id,
p.product_name
FROM products p
LEFT JOIN inventory i
ON i.product_id = p.product_id
AND i.status = 'ACTIVE'
WHERE i.inventory_id IS NULL;This returns products that lack an active inventory row. A product with only inactive inventory still appears, because inactive rows do not satisfy the join condition. Moving i.status = 'ACTIVE' into the WHERE clause would change the meaning, because the filter would run after the outer join result was formed and would no longer define the match itself.
The null check should point at a column that proves the right row existed. Checking a nullable business column can create false missing matches. If inventory.location_code allows NULL, then this query can return products that actually do have an inventory row:
SELECT
p.product_id,
p.product_name
FROM products p
LEFT JOIN inventory i
ON i.product_id = p.product_id
WHERE i.location_code IS NULL;That query tests for a missing location code, not necessarily a missing inventory row. If a product has an inventory row with a null location, it would pass the filter. For missing-row logic, the null check should usually target a right-side primary key or another column that cannot be null when a match exists.
LEFT JOIN can fit well when the query is already written in terms of joining related tables and the missing-match check reads naturally from the joined result. It also makes the null-filled output behavior visible, which can help when reviewing why certain rows appear. The tradeoff is that the reader has to keep the join condition and final null filter separate.
EXCEPT
Set difference expresses the missing-match question as one result set minus another. The first query returns the candidate rows. The second query returns rows that should be removed. EXCEPT returns rows from the first query that do not appear in the second query.
This form is compact when both sides naturally return the same column list. If you only need ids for customers who need a reminder but already have a table of customers who have responded, EXCEPT can read close to the business question.
SELECT customer_id
FROM reminder_targets
EXCEPT
SELECT customer_id
FROM reminder_responses;Both sides of EXCEPT need compatible column lists in the same order. If the first query returns one column, the second query should return one comparable column. If the first query returns two columns, the second query should return two comparable columns in the same positions. EXCEPT compares the rows produced by the SELECT lists, not the full base tables behind those queries.
That projection rule is also important. If the query returns only customer_id, then only customer_id participates in the set difference. If it returns customer_id and campaign_id, then the pair of values is compared as the row.
SELECT
customer_id,
campaign_id
FROM reminder_targets
EXCEPT
SELECT
customer_id,
campaign_id
FROM reminder_responses;This version answers a more specific question. It finds customer and campaign pairs that were targeted but have no matching response pair. A customer who responded to campaign 10 could still appear for campaign 11 if that second pair is absent from the response table.
Distinct handling is part of the meaning of plain EXCEPT. PostgreSQL treats EXCEPT as distinct by default, with EXCEPT ALL available for duplicate-preserving set difference. SQL Server’s EXCEPT returns distinct rows from the left input that do not appear in the right input. That means duplicate projected rows are not returned repeatedly with plain EXCEPT.
Support for duplicate-preserving set difference varies by database engine. PostgreSQL supports EXCEPT ALL, while SQL Server’s EXCEPT returns distinct rows from the left input that do not appear in the right input. Current Oracle and MySQL support EXCEPT, but duplicate-handling details depend on the engine being used.
EXCEPT fits best when the missing-match question is truly about comparing two result sets. It becomes less natural when the output needs full left-side rows while the comparison uses only part of those rows. In that case, a query may first find missing ids with EXCEPT, then use those ids to pull the full rows, but that reads differently from a direct NOT EXISTS check.
Choosing Among Them
These three forms point toward the same relational idea, but they do not read the same way. NOT EXISTS reads like a row-by-row absence check. LEFT JOIN with a null check reads like an outer join result being filtered down to rows that failed to match. EXCEPT reads like subtracting one set of projected rows from another. The best fit depends on the question you want the query to communicate.
NOT EXISTS is a strong default when the match depends on the current row from the left table. It handles extra predicates, date ranges, composite relationships, and nullable right-side values without forcing the final output to include right-side columns. The query stays centered on the left table, while the subquery carries the match test.
LEFT JOIN with a null check fits well when the outer join behavior is useful to the reader. It can be readable for missing child rows, such as products with no inventory row or accounts with no billing profile. The main care point is filter placement. Match rules belong in the ON clause, and the final WHERE clause should test a right-side column that proves a row was absent.
EXCEPT is a good fit for pure set difference. It is most natural when both sides return the same projected columns and the desired output is exactly the rows from the first result set that are missing from the second. The projection is part of the comparison, so changing the selected columns changes the meaning of the query.
For interview-style SQL, NOT EXISTS is usually the safest answer to explain first because it states the absence test directly and avoids null-related surprises from NOT IN. LEFT JOIN is worth learning because it is common in reports and connects well to outer-join output. EXCEPT rounds out the set-based version of the same idea, with the extra detail that plain EXCEPT works with distinct projected rows in engines such as PostgreSQL and SQL Server.
Conclusion
Anti joins come down to keeping the left-side row only when the right side cannot produce a match. NOT EXISTS handles that as a presence check, LEFT JOIN exposes failed matches through right-side NULL values, and EXCEPT treats the same idea as set difference between two projected results. The syntax changes, but the core mechanic stays the same. SQL checks for a related row, rejects the left row when that match exists, and keeps it when the match is absent.
