SQL
Find Records in One Table Not Present in Another (Anti-Join)
Discover how to identify records in a primary table that do not have a corresponding entry in a related table using a LEFT JOIN with an IS NULL condition, crucial for data integrity checks.
SELECT
p.product_id,
p.product_name
FROM
products p
LEFT JOIN
order_items oi ON p.product_id = oi.product_id
WHERE
oi.product_id IS NULL;
How it works: This SQL snippet demonstrates how to find products that have never been ordered. It uses a `LEFT JOIN` to connect the `products` table with `order_items`. When a product has no corresponding entry in `order_items`, the columns from `order_items` will be `NULL`. The `WHERE oi.product_id IS NULL` clause then filters for exactly these un-ordered products, effectively performing an anti-join operation.