Blinkit hard NOT INSubqueryLEFT JOINIS NOT NULL

Return the product_name of every product in blinkit_products whose supplier_id has never appeared in blinkit_quality_flags — meaning that supplier has zero quality complaints on record. Use a NOT IN subquery against blinkit_quality_flags, and make sure the unresolved anonymous complaint (supplier_id IS NULL) doesn't wrongly disqualify every supplier. Sort by product_name.

Blinkit · Supplier Quality Flags — practice this real-world SQL scenario live in your browser.

📖 Story Blinkit · Supplier Quality Flags

Friday evening at Blinkit's Supplier Quality team. blinkit_products lists every product currently live on the app along with the supplier_id that stocks it, and blinkit_quality_flags logs a row every time a supplier gets flagged for a complaint. Three suppliers have been flagged for concrete reasons — packaging damage, delivery delays, stale inventory — but one complaint came in anonymously through the app's feedback form before anyone could trace it back to a specific supplier, so that flag row has supplier_id = NULL. That's a genuine 'we don't know which supplier yet,' not 'no supplier at all,' and it's still sitting in the table waiting to be resolved. Procurement wants a list of every product whose supplier has never been flagged, so those suppliers can be auto-approved for the next reorder cycle without a manual review.

🎯 Your Mission

📋 Table Structure

🗂 blinkit_products

product_id INTEGER 1

product_name TEXT Amul Milk 1L

category TEXT Dairy

supplier_id INTEGER 101

🗂 blinkit_quality_flags

flag_id INTEGER 1

supplier_id INTEGER 104

reason TEXT Packaging damage complaints

⚡ Step-by-Step Walkthrough

See how products line up against flagged suppliers

query.sql

SELECT p.product_name, p.supplier_id, f.reason
FROM blinkit_products p
LEFT JOIN blinkit_quality_flags f ON f.supplier_id = p.supplier_id
ORDER BY p.product_id;

💡 Explanation

blinkit_products has 8 rows across 6 distinct supplier_ids (101 through 106), and this LEFT JOIN pulls in the flag reason for whichever supplier stocks each product, if one exists.
Lays Chips and Fortune Oil share supplier_id 103, and Real Juice (104) and Nescafe Coffee (105) each have their own flagged supplier — reason shows up for all four of these rows, everything else shows NULL.
Supplier 106 was blacklisted after a past recall but doesn't currently supply any live product, so it never shows up in this result at all — it's a flagged supplier, just not one attached to anything in blinkit_products right now.
blinkit_quality_flags actually has a fifth row too — an anonymous complaint with supplier_id = NULL — but NULL can never equal p.supplier_id for any product, so it's completely invisible here. That row hasn't gone away; it's just waiting in the next step to cause trouble.

See the trap: NOT IN returns nothing at all

query.sql

SELECT product_name
FROM blinkit_products
WHERE supplier_id NOT IN (SELECT supplier_id FROM blinkit_quality_flags)
ORDER BY product_name;

💡 Explanation

This query returns zero rows — not just the four genuinely flagged products, every single product in blinkit_products disappears, including Amul Milk 1L and Britannia Bread whose suppliers have never been flagged for anything.
The subquery's result list is {104, NULL, 105, 103, 106}, and NOT IN checks a value against every item in that list using AND logic underneath: supplier_id <> 104 AND supplier_id <> NULL AND supplier_id <> 105 AND so on.
supplier_id <> NULL never evaluates to TRUE for any value, real or not — it evaluates to NULL, or 'unknown,' every single time. Since one leg of that AND chain is always NULL, the whole condition can never be TRUE, no matter what the other legs say.
This is exactly why the anonymous flag mattered even though it showed nothing in step 1 — a single NULL anywhere in a NOT IN subquery's result set is enough to silently zero out the entire outer query, with no error message warning anyone it happened.

Fix it: filter out the NULL before NOT IN ever sees it

query.sql

SELECT product_name
FROM blinkit_products
WHERE supplier_id NOT IN (SELECT supplier_id FROM blinkit_quality_flags WHERE supplier_id IS NOT NULL)
ORDER BY product_name;

💡 Explanation

Adding WHERE supplier_id IS NOT NULL inside the subquery drops the anonymous complaint before it ever reaches the outer NOT IN, shrinking the list to a clean {103, 104, 105, 106} with no NULL left to poison anything.
Four products come back — Amul Milk 1L, Britannia Bread, Maggi Noodles, and Parle-G Biscuits — exactly the products whose suppliers (101 and 102) never appear in blinkit_quality_flags for any reason, resolved or not.
The unresolved anonymous complaint isn't lost, it's just correctly excluded from this particular check — once compliance traces it to a real supplier_id, that supplier's products will start disappearing from this list on their own, no query change needed.
The general lesson: EXISTS / NOT EXISTS never has this problem because a correlated subquery compares row by row instead of building one list with AND, but if NOT IN is what you reach for, always guard the subquery's column with IS NOT NULL first.

▶ Practice this live ← Browse all questions