1. Using LEFT JOIN ... WHERE NULL

This is the most classic and portable method.

Example

Find rows in TableA that don’t exist in TableB:

SELECT A.*
FROM TableA A
LEFT JOIN TableB B ON A.id = B.id
WHERE B.id IS NULL;

• LEFT JOIN returns all rows from TableA and matches from TableB.
• Where TableB.id is NULL, it means there was no match — the record exists only in TableA.

✅ Best for readability and widely supported.

2. Using NOT IN

Another way:

SELECT *
FROM TableA
WHERE id NOT IN (SELECT id FROM TableB);

• Select all ids from TableB.
• Return rows from TableA where id is NOT IN that list.

⚠️ Be careful: NOT IN behaves badly if there are NULL values inside the subquery — it might return no rows at all.

3. Using NOT EXISTS

This is often the best for performance, especially on large datasets:

SELECT *
FROM TableA A
WHERE NOT EXISTS (
    SELECT 1
    FROM TableB B
    WHERE A.id = B.id
);

For each row in TableA, check if there’s a matching id in TableB.

If NOT EXISTS, it means A.id is not present in B.

✅ NOT EXISTS handles NULLs properly and is often faster for large tables.

Which Method is Best?

Method	Pros	Cons
LEFT JOIN ... IS NULL	Simple, readable	Can be slower on large tables if not indexed
NOT IN	Simple syntax	Can be broken by NULLs
NOT EXISTS	Reliable, handles NULLs, often faster	More verbose syntax

Real-World Example

Imagine:

• Customers table — all customers.
• Orders table — customers who have placed orders.

Find customers who never placed an order:

SELECT C.*
FROM Customers C
LEFT JOIN Orders O ON C.customer_id = O.customer_id
WHERE O.customer_id IS NULL;

or:

SELECT *
FROM Customers
WHERE NOT EXISTS (
    SELECT 1 FROM Orders
    WHERE Customers.customer_id = Orders.customer_id
);

In Short

To find records in one table but not in another, use LEFT JOIN … IS NULL, NOT IN, or NOT EXISTS — depending on what works best for your data and DBMS.