Skip to the content
Start

What a JOIN leaves out

SQL · Unit 7: Joining tables

The owner wants to send a New Year's greeting to every customer, and pulls the names from the list of orders with names. The store has 8 customers. Are they all there?

There are six names. Hannah and Brian are missing. You already knew they weren't in orders. This time the customers table is in the query, and they still don't show up. A JOIN returns pairs: an order with its customer. No order points to them.

SELECT DISTINCT c.name
FROM orders o
JOIN customers c
  ON o.customer_id = c.id
ORDER BY c.name;

Returns

name
Ann
Chloë
Dylan
Luke
Mason
Valerie

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. 1. Multiple choice

    Luke and Hannah live in Austin. This query returns three rows, all three of them Luke's. Why is Hannah missing?

  2. 2. Predict the output

    The sales report from unit 5 had to say 0 on the Speaker's line. Someone redoes it with a JOIN, so it shows the names. 3 units of the Headphones have been ordered. What does it return for the Audio section?

  3. 3. Predict the output

    The store has 8 customers and 12 orders, and six customers have ordered something. What does this query return?

  4. 4. Find the bug

    How many Denver customers have bought something? Three customers live in Denver, but the query says 5 have bought. Which line has the problem?

  5. 5. Put the lines in order

    Who has placed an order of 2 units or more? Put in order the query that gives each customer only once, with their number and their name, in alphabetical order. Two lines don't belong.

  6. 6. Complete the code

    Fill in the condition that keeps, out of those 96 pairs, the 12 that make sense.

  7. 7. Multiple choice

    The store has sold 2289.85 in all. Here this query runs without an error and says 48376.5. What happened?

  8. 8. Multiple choice

    In unit 4 you saw that comparing something with NULL is never true. There are 8 books and 5 authors. How many rows does this query return?

  9. 9. Predict the output

    Nora Page is the only author from Scotland, and books has nothing of hers. What does this query return?

  10. 10. Find the case that fails

    The online catalog says how many books published since 2000 the library has, whether or not anyone knows who wrote them. Today that's 5, and they all have an author. Someone started from the list of books with their author and put a COUNT on it. Which of these changes makes it count one book too few?

  11. 11. Write the code

    How many books does the library have by each author? Write a query with two columns, in this order: the author's name and how many of their books there are. First whoever has the most, and ties go by name in alphabetical order.

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 →