SQL
Identify Records Lacking Related Data Using Anti-Join
Discover how to efficiently find all records in one table that do not have a matching entry in another related table using a LEFT JOIN and NULL check.
SELECT
c.customer_id,
c.customer_name,
c.email
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
WHERE
o.order_id IS NULL;
How it works: This query uses an anti-join pattern to find customers who have not placed any orders. It performs a `LEFT JOIN` from the `customers` table to the `orders` table. For customers with no corresponding orders, the columns from the `orders` table will be `NULL`. By filtering `WHERE o.order_id IS NULL`, we effectively select only those customers who do not have any matching entries in the `orders` table, making it easy to identify 'inactive' customers or orphaned records.