What is an anti-join and when would you use it?

Anti-join finds rows in the left table that have no matching rows in the right table. Use LEFT JOIN + WHERE right_table.primary_key IS NULL. Common uses: find customers without orders, products without sales, employees without reviews. Essential for gap analysis and data quality checks.

What is the difference between LEFT JOIN + IS NULL and NOT EXISTS?

Both find non-matching rows but work differently. LEFT JOIN + IS NULL creates full result set then filters. NOT EXISTS stops at first match per row. NOT EXISTS often performs better on large datasets and handles NULL values correctly. LEFT JOIN + IS NULL can be more readable.

Why should you check the primary key, not foreign key, for NULL in anti-joins?

Primary key uniquely identifies each row and is never NULL in matched rows. Foreign key can be NULL for business reasons unrelated to the join. Checking foreign key for NULL may include rows that actually have matches but with NULL foreign keys.

Find all products that have never been ordered.

SELECT p.product_id, p.product_name, p.price

FROM products p

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

WHERE oi.order_item_id IS NULL

ORDER BY p.product_name;

LEFT JOIN preserves all products. WHERE oi.order_item_id IS NULL finds products without order_items records.

Write a query to find customers who registered but never placed an order.

SELECT c.customer_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;

Anti-join pattern finds customers without orders. Check orders.order_id (primary key) for NULL, not customer_id.

Find employees who have not submitted timesheets for the current month.

SELECT e.employee_name, e.department

FROM employees e

LEFT JOIN timesheets t ON e.employee_id = t.employee_id

AND YEAR(t.submit_date) = YEAR(CURRENT_DATE)

AND MONTH(t.submit_date) = MONTH(CURRENT_DATE)

WHERE t.timesheet_id IS NULL

AND e.status = 'active';

Complex anti-join with date conditions in ON clause. Finds active employees without current month timesheets.