rag/sample_templates/07_exists_not_exists.sql

-- Template: EXISTS and NOT EXISTS Set Operations
-- Pattern: Efficient subquery filtering for existence checks
-- Use Case: Find records that match/don't match conditions in other tables

-- Example 1: Customers who placed orders in 2024
SELECT 
    c.customer_id,
    c.name,
    c.email
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= '2024-01-01'
      AND o.order_date < '2025-01-01'
);

-- Example 2: Products ordered in 2023 but not in 2024
SELECT 
    p.product_id,
    p.name,
    p.category
FROM products p
WHERE EXISTS (
    SELECT 1 
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE oi.product_id = p.product_id
      AND o.order_date >= '2023-01-01'
      AND o.order_date < '2024-01-01'
)
AND NOT EXISTS (
    SELECT 1 
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE oi.product_id = p.product_id
      AND o.order_date >= '2024-01-01'
      AND o.order_date < '2025-01-01'
);

-- Key Concepts:
-- - EXISTS returns TRUE if subquery returns any rows
-- - More efficient than IN for large datasets
-- - Better NULL handling than IN/NOT IN
-- - Short-circuits: stops at first match
-- - Use SELECT 1 (constant) instead of SELECT * for efficiency