Lesson 8 of 12 · 9 min

LEFT JOIN and missing data

A normal JOIN (also called INNER JOIN) only keeps rows that have a match on both sides. A customer with no orders simply disappears.

A LEFT JOIN keeps every row from the left table. Where there is no match, the columns from the right table are filled with NULL:

SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;
name order_id
Tariro 1
... ...
Kuda NULL

NULL means "no value"

NULL is not zero and not empty text: it means "unknown" or "nothing here". Because of that, = NULL never matches anything. Always test with IS NULL or IS NOT NULL:

SELECT name FROM customers WHERE city IS NULL;

Finding what is missing

LEFT JOIN plus IS NULL is the classic way to find things with no match: customers who never ordered, products never sold, students with no marks.

Your task

Show the name of every customer who has never placed an order, sorted by name.

query.sql
-- Customers with no orders

SQLite runs right here in your browser. The first run downloads about 0.7 MB; after that it is instant.

Discussion

Stuck? Ask here. Be kind, and share hints rather than full answers.

Loading the discussion…