Fulfledge

Join tables together

INPUT · Slides

Joining while keeping one side whole

01 / 05

Some rows disappear in a join

A JOIN returns only the rows where the key matched. So a member who has never ordered drops out of the result.

Take the names out with no repeats below and only four appear. members holds five, and Nonoka is not there.

SELECT DISTINCT members.name  FROM members  JOIN orders  ON members.id = orders.member_id  ORDER BY members.name;

Result

Itsuki
Kaede
Minato
Suzuna

02 / 05

Sometimes disappearing is a problem

"I want to write to the members who have not bought anything yet" — that is a question whose answer lives entirely in the rows that disappeared.

You want to join while keeping the members with no orders in the table. The way to write that is LEFT JOIN.

03 / 05

LEFT JOIN keeps the left

You only change JOIN to LEFT JOIN. Every row of the left table, the one written in FROM, is kept; the right side is added where there is a partner and left empty where there is not.

The result below has one more row than before, ten in all. Nonoka is still there at the bottom.

SELECT members.name,  orders.quantity, members.city  FROM members  LEFT JOIN orders  ON members.id = orders.member_id  ORDER BY members.id, orders.id;

Result

Minato | 2 | Aoba City
Minato | 4 | Aoba City
Minato | 6 | Aoba City
Suzuna | 4 | Hinata Town
Suzuna | 3 | Hinata Town
Kaede | 1 | Aoba City
Kaede | 2 | Aoba City
Itsuki | 3 | Mizuumi City
Itsuki | 2 | Mizuumi City
Nonoka |  | Hinata Town

04 / 05

The gaps are NULL

Nonoka's quantity was left empty. That is NULL — no value. There is no partner row, so the right-hand columns cannot be filled in.

Which means you can hunt for "the rows that had no partner" with IS NULL. The IS NULL you learned in chapter 1 comes into its own here.

SELECT members.name  FROM members  LEFT JOIN orders  ON members.id = orders.member_id  WHERE orders.id IS NULL;

Result

Nonoka

05 / 05

The same on the items side

The same move gets you "the items nobody has ever ordered". Put items on the left and LEFT JOIN orders.

Finding what is not selling, finding the people your letters have not reached — looking for what is not there is where LEFT JOIN shines. Let us write some.

SELECT items.name  FROM items  LEFT JOIN orders  ON items.id = orders.item_id  WHERE orders.id IS NULL;

Result

moonlit vase