Anti Join Pattern — Solution & ExplanationProblems / Anti Join Pattern / EditorialApproach

LEFT JOIN products to order_items on product_id. Products that were never ordered will have NULL values for all order_items columns.

Filter with WHERE oi.item_id IS NULL to keep only those unmatched products.

Select the product columns and order by product_id.

Key InsightThe anti-join pattern (LEFT JOIN + IS NULL) is equivalent to NOT IN or NOT EXISTS. In PySpark, left_anti is a dedicated join type that does this in one step. Products 6 (Webcam), 8 (Charger), 9 (Speaker), and 10 (Stylus) have no orders.Hints

LEFT JOIN + WHERE right_side IS NULL is the anti-join pattern.

Check for NULL on a non-nullable column from order_items (like item_id).

In PySpark, you can use 'left_anti' join type directly.

Solve Anti Join Pattern yourself →