Never-Ordered Products

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

Google

Problem

Which products in the catalog have never been ordered? Show product ID and product name for items with no matching transaction.

In the catalog. Never purchased.

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

What this really is

This is an anti-join dressed up as a catalog audit: list the rows of products that have no partner at all in transactions. Anyone can relate the two tables on product_id. What separates candidates is remembering that a plain join only keeps pairs that matched. The products the question is about are gone before any filter runs. Write the inner join and you get the 91 products that have sold, the exact opposite of the 109 that never have.

Building it

Step 1: Keep every product, matched or not

LEFT JOIN transactions t ON p.product_id = t.product_id keeps each row of products even when no transaction points at it. Unmatched products come through once, with every t column set to NULL. A product that sold fans out to one row per sale, which does not matter because those rows are about to be discarded.

Step 2: Test a column a real match always fills

WHERE t.transaction_id IS NULL keeps only the padded rows. Test the right side's key, not a measure like t.quantity or t.total_amount. Transaction 1335 is a voided sale with quantity 0, and it still means the product was ordered.

Step 3: Return the product

Select p.product_id and p.product_name. No dedup is needed: an unmatched product produces exactly one padded row, so each survivor appears once.

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

Inner join

JOIN transactions t ON p.product_id = t.product_id emits a row only where a sale exists. Every never-ordered product is dropped by the join itself, and no WHERE clause can bring it back.

Left join plus a null test

LEFT JOIN pads unmatched products with NULLs, and t.transaction_id IS NULL selects exactly those 109 rows.

Common follow-up questions

• Which products sold nothing in 2026? (Tests whether the date condition goes in the `ON` clause. Put in `WHERE`, it compares the padded `NULL` dates and discards every unmatched product.)
• Should a product whose only sale was refunded count as ordered? (Tests reading the business definition: transaction 1603 has a negative `total_amount`.)
• Rewrite it with `NOT EXISTS`. Why is that safer than `NOT IN` here? (Tests null semantics in subquery predicates over a nullable `product_id`.)