Find every store-product listing gap

Question

A category manager wants a full coverage matrix: every store should ideally list every active product. The team needs the **gaps**.

Build the complete `store × product` grid and return `store_code`, `product_code` and `product_name` for every combination that is **missing** from `store_listings`. O

Accepted Answer

Step 1: The universe of what *should* exist is every store paired with every product — that is exactly a `CROSS JOIN` between `stores` and `products`. Step 2: `LEFT JOIN store_listings` on both keys to check which of those pairs actually exist today. Step 3: Pairs with no listing produce `NULL`s on the listing side, so `l.product_code IS NULL` isolates the gaps. Step 4: Restrict to sellable items with `p.is_active` so the discontinued gift box is not reported as a gap. Step 5: Return the store, the product code and the product name for each missing combination.

Find every store-product listing gap

More Joins — Cross Join questions

Asked at Target