SolveWithSQL

Difficulty: Beginner
Estimated time: 15–20 minutes
SQL concepts: LEFT JOIN, preserving rows, missing relationships
Goal: Understand how to keep all rows from one table—even when related data is missing.

The Scenario

After joining products with suppliers using an INNER JOIN, the product manager notices something:

“Some products don’t have a supplier yet. I still want to see all products, even if the supplier is missing.”

This is where LEFT JOIN comes in.

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

suppliers

supplier_id	supplier_name
1	TechSource
2	DisplayWorks

(Note: There is no supplier with ID NULL.)

Your Task

Write a SQL query that returns:

• Product name
• supplier_name (if available)

…and includes all products, even those without a supplier.

Expected Output

name	supplier_name
Wireless Mouse	TechSource
Mechanical Keyboard	TechSource
27-inch Monitor	DisplayWorks
USB-C Hub	NULL
Laptop Stand	DisplayWorks

Constraints

• Use LEFT JOIN
• Join products to suppliers using supplier_id
• Keep all rows from products
• Do not use SELECT *

Hint (Optional)

A LEFT JOIN:

• Keeps all rows from the left table
• Fills missing matches with NULL values

Explanation

• LEFT JOIN keeps every row from products.
• When there is no matching supplier, supplier_name becomes NULL.
• This is essential for spotting missing or incomplete relationships in data.

Common Mistakes

1. Using INNER JOIN instead of LEFT JOIN
   • This would hide products without suppliers.
2. Filtering on the joined table in WHERE
WHERE s.supplier_name IS NOT NULL -- ❌ turns it back into an INNER JOIN
3. Confusing LEFT vs RIGHT joins
   • Focus on which table you want to fully keep.

Optional Extension (Mini Bonus)

Try:

1. Replace NULL supplier names with "Unknown" using COALESCE.
2. Show only products without a supplier.
3. Add product price and sort by it.

Next Challenge

Beginner Challenge #10: Find Missing Matches with LEFT JOIN + WHERE

🔗 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.