SQL
Identify Missing Records Using LEFT JOIN and IS NULL
Discover records present in one table but absent from another using an SQL LEFT JOIN combined with an IS NULL condition for effective data reconciliation.
SELECT
p.id AS product_id,
p.name AS product_name
FROM
products p
LEFT JOIN
orders o ON p.id = o.product_id
WHERE
o.product_id IS NULL;
How it works: This query helps identify products that have never been ordered. It uses a `LEFT JOIN` to combine rows from the `products` table with matching rows from the `orders` table. If a product has no corresponding entry in the `orders` table, the columns from the `orders` table will be `NULL`. The `WHERE o.product_id IS NULL` condition then filters for these unmatched products, revealing items that might be underselling or are new.