Products Without Sales

Products Without Sales is the shortest question in the HackerRank SQL (Intermediate) test. It checks one idea: how to find rows in one table that have no match in another.

What the question asks

An online store has a product list and a table of invoice lines. Find every product that has never been sold, meaning it does not appear on any invoice line. Show each product’s SKU and name, sorted by SKU from lowest to highest.

Tables

The problem shows the table names in capitals (PRODUCT, INVOICE_ITEM). Solutions that people report passing with use both capital and lower-case names, so either works there. I use lower case.

Solution

SELECT
    p.sku,
    p.product_name
FROM product AS p
LEFT JOIN invoice_item AS ii ON ii.product_id = p.id
WHERE ii.product_id IS NULL
ORDER BY p.sku ASC;

How it works

1. LEFT JOIN keeps every product. A product that was sold gets one row per invoice line. A product that was never sold still gets one row, with every invoice_item column set to NULL.
2. WHERE ii.product_id IS NULL keeps only the unmatched ones. Those are the products with no sales. Each of them appears exactly once, so you do not need DISTINCT.
3. Sort by SKU. The question asks for SKU order, not id order. They are not always the same.

This version works the same as NOT EXISTS:

SELECT p.sku, p.product_name
FROM product AS p
WHERE NOT EXISTS (
    SELECT 1 FROM invoice_item AS ii WHERE ii.product_id = p.id
)
ORDER BY p.sku ASC;

Mistakes that fail the hidden tests

• NOT IN when the subquery can contain NULL. Most public answers use WHERE id NOT IN (SELECT product_id FROM invoice_item). It passes as long as product_id is never NULL. I added one invoice line with a NULL product_id to my test data, and the NOT IN query returned zero rows while the LEFT JOIN query still returned both unsold products. In SQL, x NOT IN (1, 2, NULL) is never true. LEFT JOIN and NOT EXISTS do not have this problem.
• Sorting by id. In my sample, Copper Bottle has a higher id but a lower SKU than Brass Diya Set. ORDER BY p.id puts them the wrong way round.
• Putting the NULL check in the ON clause. LEFT JOIN ... ON ii.product_id = p.id AND ii.product_id IS NULL matches nothing, so every product comes back. The check belongs in WHERE.
• Selecting the id instead of the SKU. The output wants sku and product_name, in that order.

Try it on sample data

Sample rows I used. The edge cases: product 6 has a lower SKU than product 5, and several products appear on more than one invoice line.

product

invoice_item

Expected output

Products 1 to 4 all appear in invoice_item. Products 5 and 6 never do. MySQL 8.0, MySQL 5.7 and SQLite all returned these two rows in this order.