Products that have never been ordered

Question

A catalogue clean-up needs the dead weight: products with zero order lines ever.

Return `sku`, `product_title` and `category` from `catalog_items` for every product that has no matching row in `order_lines`.

Accepted Answer

Step 1: Drive the query from the table you must preserve in full — `catalog_items` — and `LEFT JOIN` the order lines onto it. Step 2: Products that were ordered produce one row per order line; products never ordered produce a single row with `NULL`s on the right-hand side. Step 3: `WHERE o.line_id IS NULL` keeps only those unmatched products. Test a `NOT NULL` column such as the primary key so the check is unambiguous. Step 4: Project only the catalogue columns; nothing from the right table is meaningful in the result.

Products that have never been ordered

More Joins — Left/Right Outer Join questions

Asked at Flipkart