Finding Records Without Matching Entries in Another Table

-- Using LEFT JOIN and IS NULL
SELECT 
  p.id, 
  p.product_name
FROM 
  products p
LEFT JOIN 
  orders o ON p.id = o.product_id
WHERE 
  o.product_id IS NULL;

-- Using NOT EXISTS
SELECT 
  p.id, 
  p.product_name
FROM 
  products p
WHERE NOT EXISTS (
  SELECT 1 
  FROM orders o 
  WHERE o.product_id = p.id
);

-- Using NOT IN (less performant with large datasets or NULLs)
SELECT 
  p.id, 
  p.product_name
FROM 
  products p
WHERE 
  p.id NOT IN (SELECT product_id FROM orders WHERE product_id IS NOT NULL);

How it works: This snippet demonstrates how to find records in one table that do not have any corresponding entries in a related table, a common requirement for data cleaning or reporting. It illustrates three primary methods: using a LEFT JOIN with an IS NULL condition, employing the NOT EXISTS clause with a subquery, and utilizing NOT IN. Each method achieves the 'anti-join' effect, with LEFT JOIN/IS NULL and NOT EXISTS generally preferred for performance and robustness in most database systems.