SQL
Find Records Present in One Table But Not Another
Identify records that exist in one SQL table but have no matching entries in a second table using LEFT JOIN and WHERE IS NULL or NOT EXISTS.
-- Method 1: Using LEFT JOIN and WHERE IS NULL
-- Find customers who have not placed any orders
SELECT
c.customer_id,
c.customer_name
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
WHERE
o.order_id IS NULL;
-- Method 2: Using NOT EXISTS (often better performance for large datasets)
-- Find products that have never been ordered
SELECT
p.product_id,
p.product_name
FROM
products p
WHERE NOT EXISTS (
SELECT 1
FROM order_items oi
WHERE oi.product_id = p.product_id
);
How it works: This snippet illustrates two common ways to perform an anti-join, which identifies records in one table that do not have a corresponding entry in another table. The `LEFT JOIN` with `WHERE IS NULL` approach is often intuitive and widely supported. Alternatively, `NOT EXISTS` can be more performant for larger datasets by avoiding the overhead of a full join and stopping evaluation as soon as a non-matching row is found.