rag/sample_templates/06_left_join_missing.sql

-- Template: Finding Missing Data with LEFT JOIN
-- Pattern: Identify records that don't have matching records in related table
-- Use Case: Customers without orders, products never sold, orphaned records

-- Example 1: Customers who have never placed an order
SELECT 
    c.customer_id,
    c.name,
    c.email,
    c.registration_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.registration_date DESC;

-- Example 2: Products that have never been ordered
SELECT 
    p.product_id,
    p.name,
    p.category,
    p.price,
    p.stock_quantity
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

-- Key Concepts:
-- - LEFT JOIN keeps all records from left table
-- - WHERE column IS NULL finds unmatched records
-- - Must check a column from right table that cannot be NULL (like primary key)
-- - Alternative: NOT EXISTS (often faster for large datasets)

-- Alternative using NOT EXISTS:
SELECT 
    c.customer_id,
    c.name,
    c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);