Use LEFT JOIN + IS NULL to find rows in the left table that have no match in the right. The canonical anti-join — used for inventory pulls, abandoned-cart hunts, and 'who isn't in our CRM' questions.
Quarterly merchandising review. I want every product in `ecom_products` that has NEVER appeared in a line item — i.e., we have inventory, but no demand. Return product_id, title, vendor, inventory_qty so the merchandising team can decide whether to discount, pull, or wait. Use a LEFT JOIN to `ecom_line_items` and filter where the line-item side is NULL. Don't filter on financial_status — even a refunded order means SOMEONE wanted the product; we want zero-attempt items only.
| Column | Type | Key |
|---|---|---|
| product_id | INT | PK |
| title | TEXT | |
| vendor | TEXT | |
| product_type | TEXT | |
| price | REAL | |
| sku | TEXT | |
| inventory_qty | INT | |
| created_date | TEXT |
| Column | Type | Key |
|---|---|---|
| line_item_id | INT | PK |
| order_id | INT | FK → ecom_orders |
| product_id | INT | FK → ecom_products |
| quantity | INT | |
| price | REAL | |
| sku | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
LEFT JOIN keeps every left-side (products) row whether or not there's a matching right-side (line_items) row.
`WHERE l.line_item_id IS NULL` drops the matched rows and keeps the unmatched ones. That's the anti-join.
Expected: 1 product. The 12oz drip coffee never moved this period — small inventory, niche grind, and the 32oz cold brew concentrate dominates that vendor's mix.