SolveWithSQL

Difficulty: Beginner
Estimated time: 15–20 minutes
SQL concepts: LEFT JOIN, WHERE ... IS NULL, finding missing relationships
Goal: Identify records that don’t have a matching row in a related table.

The Scenario

After building a report that includes all products (even those without suppliers), the product manager asks:

“Which products are missing a supplier?”

This is a classic real-world data quality question.

You’ll use:

• a LEFT JOIN to keep all products, and
• a WHERE filter to return only the rows where the supplier match is missing.

Database Tables

products

column_name	type	description
product_id	INTEGER	Unique product ID
name	TEXT	Product name
category	TEXT	Product category
price	DECIMAL	Product price
supplier_id	INTEGER	References suppliers.supplier_id (can be NULL)

suppliers

column_name	type	description
supplier_id	INTEGER	Unique supplier ID
supplier_name	TEXT	Supplier name

Sample Data

products

product_id	name	category	price	supplier_id
101	Wireless Mouse	Accessories	24.99	1
102	Mechanical Keyboard	Accessories	89.00	1
103	27-inch Monitor	Displays	229.99	2
104	USB-C Hub	Accessories	34.50	NULL
105	Laptop Stand	Workspace	39.99	2
106	Webcam	Accessories	59.99	99

Notice: Product 106 has a supplier_id (99) that does not exist in suppliers.
That’s also considered “missing a supplier match.”

suppliers

supplier_id	supplier_name
1	TechSource
2	DisplayWorks

Your Task

Write a SQL query that returns:

• Product name
• supplier_id

…but only for products that do not have a matching supplier.

This includes:

• Products where supplier_id is NULL
• Products where supplier_id exists but does not match any supplier

Expected Output

name	supplier_id
USB-C Hub	NULL
Webcam	99

Constraints

• Use LEFT JOIN
• Filter for missing matches using WHERE ... IS NULL
• Do not use NOT IN for this challenge
• Do not use SELECT *

Hint (Optional)

When a LEFT JOIN can’t find a match, the joined table’s columns become NULL.
That makes it easy to filter missing matches with:

• WHERE s.supplier_id IS NULL

Explanation

• LEFT JOIN keeps all products.
• For products that don’t match a supplier, s.supplier_id becomes NULL.
• WHERE s.supplier_id IS NULL filters the result to only “missing supplier” cases.

This pattern is widely used for:

• Data quality checks
• Detecting missing relationships
• Finding orphan records

Common Mistakes

1. Filtering on the wrong NULL
   • You want to check a column from the joined table (suppliers), not the left table.
2. Using INNER JOIN
   • That would remove unmatched products before you can detect them.
3. Using WHERE p.supplier_id IS NULL only
   • That would miss cases like supplier_id = 99 (invalid reference).

Optional Extension (Mini Bonus)

Try:

1. Return missing suppliers only for Accessories.
2. Sort missing items by product name.
3. Add a computed flag:
   • CASE WHEN s.supplier_id IS NULL THEN 'Missing' ELSE 'OK' END

Next Challenge

If you want to keep going beyond Challenge #10, the next logical beginner topic is:

Beginner Challenge #11: Conditional Labels with CASE

🔗 View reference solution on GitHub
(After you’ve tried the challenge)

Want more practical SQL challenges?
Subscribe to the Solve With SQL newsletter and get new problems delivered to your inbox.