Products Without Sales

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Natera

Problem

Which products have never appeared in a transaction? Return the product ID and product name.

Listed but never sold.

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

This is a set difference wearing a catalog costume

Strip the business framing and this is products MINUS the product_ids that appear in transactions: a pure anti-join. The skill being probed is whether you can assert NON-existence, not existence. Almost everyone reaches for a join between the two tables, and that instinct is exactly the trap. A positive join can only surface products that DID sell, so inverting it in your head does not invert the SQL. The correct move is LEFT JOIN from products and keep only the rows where the transaction side failed to match. Get the direction wrong and you return the mirror image of the answer: every product that HAS sold. Get the IS NULL column wrong and you leak false positives.

Why the obvious join betrays you

The instinct (wrong)

INNER JOIN products to transactions returns only products that matched a sale. There is no WHERE clause you can bolt on to make an inner join emit the rows that were never there to begin with: an unmatched product simply produces zero rows. Candidates who start here end up computing the exact opposite set, the 91 products that sold.

The anti-join (right)

LEFT JOIN keeps every product (all 200 in the catalog) and pads unmatched ones with NULLs on the transaction side. Filtering WHERE t.transaction_id IS NULL keeps precisely the 109 products for which no transaction row existed. That is the set difference expressed directly.

Building it

Step 1: Anchor on products with a LEFT JOIN

products goes on the left so every catalog row survives the join regardless of sales. If you write it as transactions LEFT JOIN products you are back to only ever seeing rows that have a transaction, which cannot express never-sold.

Step 2: Match on the shared key

ON p.product_id = t.product_id is the linkage. Matched products get one or more transaction rows attached; unmatched products get a single row with the entire transaction side NULL.

Step 3: Keep only the misses, on a non-null key

WHERE t.transaction_id IS NULL selects the padded rows. transaction_id is the transactions primary key, so it is never null in a real match, that is what makes the IS NULL test a reliable never-matched signal rather than a coincidence of missing data.

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN transactions t ON p.product_id = t.product_id
WHERE t.transaction_id IS NULL

Common follow-up questions

• Rewrite this as NOT EXISTS. Is the result identical, and is the plan? (Tests whether the candidate knows NOT EXISTS is the same anti-join semantics and that most optimizers produce the same anti-join plan.)
• A colleague suggests WHERE `product_id` NOT IN (SELECT `product_id` FROM transactions). Why is that dangerous here? (Tests the NOT IN null trap: 18 sales here carry a NULL `product_id`, and a single NULL in the subquery makes NOT IN return no rows at all.)
• `transactions.product_id` has only ~13,000 distinct values in 40M rows. What index makes this anti-join cheap, and why does low cardinality help? (Tests indexing intuition for a high-fan-out foreign key and index-only anti-join probes.)