Basic Anti-Join - Customers Without Orders

basic

Find customers who have never placed an order for marketing campaigns

-- Anti-join pattern: LEFT JOIN + WHERE IS NULL

SELECT c.customer_id, c.customer_name, c.email, c.registration_date

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

WHERE o.customer_id IS NULL -- Check right table PRIMARY KEY

ORDER BY c.registration_date DESC;

-- Alternative: NOT EXISTS (often faster)

SELECT c.customer_id, c.customer_name, c.email, c.registration_date

FROM customers c

WHERE NOT EXISTS (

SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id

)

ORDER BY c.registration_date DESC;

Returns customers who have never placed orders

LEFT JOIN preserves all customers. WHERE o.customer_id IS NULL finds customers without matches in orders table.

All

Products Never Sold - Inventory Analysis

intermediate

Find products that have never been ordered for clearance or discontinuation

-- Find unsold products with additional context

SELECT

p.product_id,

p.product_name,

p.category,

p.price,

p.stock_quantity,

p.created_date,

DATEDIFF(CURRENT_DATE, p.created_date) as days_in_catalog

FROM products p

LEFT JOIN order_items oi ON p.product_id = oi.product_id

WHERE oi.product_id IS NULL -- No sales records

AND p.status = 'active'

AND p.created_date < CURRENT_DATE - INTERVAL 30 DAY -- At least 30 days old

ORDER BY p.created_date ASC;

Shows products never sold with context like stock levels and time in catalog

Anti-join finds products without order_items records. Additional filters for active products older than 30 days.

All