Finding Records That Exist in One Table but Not Another

This operation is known as an anti-join.

Assume these tables:

Customers

Orders

Requirement: Find customers who have not placed any orders.

Expected result:

1. Using NOT EXISTS — recommended

SELECT
    C.CustomerId,
    C.CustomerName
FROM Customers C
WHERE NOT EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerId = C.CustomerId
);

How it works

For each customer, SQL Server checks whether a matching order exists:

• Match exists → customer is excluded.

• No match exists → customer is returned.

Why it is preferred

• Handles NULL values safely.

• Clearly expresses the requirement.

• Does not create duplicate customer rows.

• Usually produces an efficient execution plan with proper indexes.

2. Using LEFT JOIN

SELECT
    C.CustomerId,
    C.CustomerName
FROM Customers C
LEFT JOIN Orders O
    ON O.CustomerId = C.CustomerId
WHERE O.CustomerId IS NULL;

How it works

The LEFT JOIN returns all customers.

For customers without orders, the Orders columns contain NULL. The WHERE clause selects only those unmatched records.

Important point

Check a non-nullable column from the right table, preferably its primary key:

WHERE O.OrderId IS NULL

This avoids confusion if the joined column is allowed to contain NULL.

3. Using EXCEPT

SELECT CustomerId
FROM Customers

EXCEPT

SELECT CustomerId
FROM Orders;

Result

3

EXCEPT returns distinct values from the first query that do not appear in the second query.

To return customer details:

SELECT
    C.CustomerId,
    C.CustomerName
FROM Customers C
INNER JOIN
(
    SELECT CustomerId
    FROM Customers

    EXCEPT

    SELECT CustomerId
    FROM Orders
) Missing
    ON Missing.CustomerId = C.CustomerId;

Important rules

Both queries must return:

• The same number of columns

• Columns in the same order

• Compatible data types

EXCEPT automatically removes duplicate rows.

4. Using NOT IN

SELECT
    C.CustomerId,
    C.CustomerName
FROM Customers C
WHERE C.CustomerId NOT IN
(
    SELECT O.CustomerId
    FROM Orders O
    WHERE O.CustomerId IS NOT NULL
);

Important warning

If the subquery returns a NULL, NOT IN may return no rows due to SQL’s three-valued logic.

Therefore, either:

• Filter out NULL values, or

• Prefer NOT EXISTS.

Comparison

Find orders without valid customers

Reverse the table order:

SELECT
    O.OrderId,
    O.CustomerId
FROM Orders O
WHERE NOT EXISTS
(
    SELECT 1
    FROM Customers C
    WHERE C.CustomerId = O.CustomerId
);

This can help identify orphaned records.

Find differences in both directions

To identify records that exist in either table but not both:

SELECT
    C.CustomerId,
    'Only in Customers' AS Location
FROM Customers C
WHERE NOT EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerId = C.CustomerId
)

UNION ALL

SELECT
    O.CustomerId,
    'Only in Orders' AS Location
FROM Orders O
WHERE NOT EXISTS
(
    SELECT 1
    FROM Customers C
    WHERE C.CustomerId = O.CustomerId
);

This is useful for data reconciliation.

Performance recommendation

Create an index on the comparison column:

CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId);

The customer primary key is normally already indexed.

For large tables, check the actual execution plan. SQL Server may implement NOT EXISTS as a Left Anti Semi Join.

Key points

• This operation is called an anti-join.

• Prefer NOT EXISTS for most requirements.

• LEFT JOIN ... IS NULL is a common alternative.

• Use EXCEPT when comparing complete datasets.

• Avoid NOT IN when the subquery can contain NULL.

• Compare columns with compatible data types.

• Index the columns used for matching.

• Define clearly which table is the source and which table is used for comparison.

Interview answer:
“To find records present in one table but absent from another, I normally use NOT EXISTS. A LEFT JOIN followed by WHERE RightTable.PrimaryKey IS NULL is another good option. EXCEPT is useful for set comparison, while NOT IN must be used carefully because NULL in the subquery can cause unexpected results.”