Skip to the content
Start

Finding what's missing

SQL · Unit 8: More joins

Who hasn't the store sold anything to yet? The owner wants to send them a welcome discount. The question is about something that isn't there, and until today no query could look for an absence.

The LEFT JOIN puts it in plain sight. Anyone with no orders shows up with the columns of orders set to NULL, and a WHERE with IS NULL keeps exactly those rows.

SELECT c.name, o.id, o.placed
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE o.id IS NULL;

Returns

nameidplaced
HannahNULLNULL
BrianNULLNULL

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. Complete the code

    Which product has never been sold? Fill in the blank with the column that only comes back empty when there's no order.

  2. 2. Predict the output

    Ann, Valerie, and Brian live in Denver. Luke and Hannah live in Austin. What does this query return?

  3. 3. Put the lines in order

    The welcome discount goes out by mail, and the envelopes are prepared in alphabetical order. Put in order the query that gives the name and the city of everyone who hasn't ordered anything. Two lines don't belong.

  4. 4. Multiple choice

    Which of these questions can't be answered with a single table or with a plain JOIN?

  5. 5. Predict the output

    In unit 5 you saw that COUNT(*) counts rows, and COUNT(column) counts the rows where that column isn't NULL. Dylan has two orders. Hannah and Brian have none. What does this query return?

  6. 6. Find the bug

    To decide what to restock, the owner counts how many times each out-of-stock product has been ordered. There are two orders for the Cable and none for the Speaker. The report says the Speaker has been ordered once. Which line has the problem?

  7. 7. Multiple choice

    A report of orders per customer, built with COUNT(*) over the LEFT JOIN, says 1 today for Hannah, who hasn't ordered anything. Tomorrow Hannah places her first order. What will that report say?

  8. 8. Put the lines in order

    Who has bought only once, or never? The store wants to write to them. Put in order the query that gives the name and how many orders each one has placed, fewest first and, on a tie, by name. Anyone who hasn't ordered shows up with a 0. Two lines don't belong.

  9. 9. Complete the code

    How many units has each Austin customer ordered? Luke lives there, with three orders, and so does Hannah, with none. Fill in the sum.

  10. 10. Write the code

    The full sales report, with its eight lines. Write a query with two columns, in this order: the name of each product and how many units of it have been ordered, with a 0 for the one nobody has ordered. Most ordered first, and ties by name.

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 →