LEFT JOIN

SQL · Unit 8: More joins

Full plan
SQL
11 exercises

Last unit left a list half done. The owner used the list of orders with names to pick who gets a New Year's greeting, and six of the eight names showed up. For a greeting, customers was enough. Now the owner wants more: to write to every customer with their orders in hand, including the ones who have none. Hannah and Brian haven't ordered anything, and the JOIN left them out.

LEFT JOIN keeps every row of the table in the FROM, whether it has a match or not. These are the two Austin customers. Luke shows up three times, as before. Hannah shows up once, with the date empty, because there's no order to put next to her.

SELECT c.name, o.placed
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE c.city = 'Austin';

Returns

name	placed
Luke	2026-01-04
Luke	2026-01-11
Luke	2026-01-20
Hannah	NULL

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Predict the output
Customers 6, 7, and 8 are Dylan, Hannah, and Brian. Dylan has placed orders 9 and 11. The other two haven't placed any. What does this query return?
2. Put the lines in order
What orders has each product in the Audio section gotten? Every product has to be there, including the one nobody has ordered. Put in order the query that shows the product and the quantity of each order, most expensive product first and, within each one, by order number. Two lines don't belong.
3. Multiple choice
The store has 8 customers and 12 orders, and two customers haven't ordered anything. How many rows does this query return?
4. Predict the output
Chloë is customer 3. She has no city on file, and she has placed one order, on January 15. Hannah is customer 7, lives in Austin, and hasn't ordered anything. What does this query return?
5. Complete the code
The list of customers 3, 7, and 8 with the date of their order, saying no orders where there isn't one. Fill in the blank with the column that comes back empty when someone hasn't ordered.
6. Complete the code
Before restocking, the owner looks at which days each out-of-stock product was ordered. Two have stock 0, the Cable and the Speaker, and both have to be there. Fill in the blanks, each line with its table.
7. Multiple choice
In someone else's query you read FROM orders o RIGHT JOIN customers c, with the usual ON. Which of these gives the same rows?
8. Find the bug
The store is opening its Denver shop and inviting its three customers there to the party, whether they've bought anything or not. The list shows each guest with their order numbers. The query runs without an error, and Brian, who lives in Denver, isn't on the list. Which line has the problem?
9. Put the lines in order
The store is putting together a deal for its customers aged 30 to 49, whether they've bought anything or not. Put in order the query that shows the name, the age, and the date of each order, youngest customer to oldest and, within each one, by date. Two lines don't belong.
10. Find the case that fails
The owner is traveling to Tampa with the list of every customer there, with their order numbers if they have any. Today only Mason lives in Tampa, and he has one order. Someone wrote the query with orders in the FROM. Which of these changes makes it leave someone from Tampa off the list?
11. Write the code
The list the owner wants now, with all eight customers and what each one has ordered. Write a query with three columns, in this order: the customer's name; their city, or No city if it isn't on file; and the date of each of their orders, or no orders if they have none. One row per order, and one for anyone with none, by name and, within each customer, oldest order to newest.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →