In Chapter I you walked a single bed — the rose bed — and learned to fill a basket with flowers that matched your criteria. But the garden is more than its flowers. Bees visit them. Each bee remembers the flowers it has touched.

Which bee visited which flower? Which flowers haven't been pollinated yet? Which bees are new to the garden and haven't found a flower at all? Every one of these crosses between two tables. You need both sides to answer it.

That's what this chapter is about: the JOIN — the path between two tables, and a bee flying it, pairing each visitor with the flower it has visited.

The first new table is called bees. It holds the pollinators that visit the flowers — three of them, each with her own habits:

• Buzz — the focused one. She picks a single flower and stays with it.
• Honey — the productive one. She travels widely and visits more than one bloom.
• Bumble — the wanderer. She's still looking for her first landing of the day.

Before we look at the table, here they are in the air:

Just like a flower's tag, each bee has an id (a unique number) and a name. Nothing more, for now.

By themselves the bees aren't very interesting — you can't tell from this table alone which flowers they have visited. For that, you'll need a second table.

The second table is called flowers. It holds the flowers in the bed — four of them: a Marigold, a Jasmine, a Lotus, and a Tulip. Before we look at the table, here they are in the bed:

Each flower has a name and — this is the new part — a pollinated_by column that holds the id of the bee that has pollinated it. It's a pointer back into the bees table.

Two things to notice. First: the Marigold has pollinated_by = 1, and bee number 1 in the other table is Buzz. So the Marigold was visited by Buzz — even though her name doesn't appear in this table at all.

Second: the Tulip has pollinated_by = NULL (the garden word for "nothing" — no bee has reached it). It waits.

Also worth noticing: Honey (id 2) has visited two flowers. One row in bees, two rows in flowers that point back to it. That's normal — one bee, many visits.

Look at the two tables side by side. Notice how the pollinated_by in the flowers table is just a number that matches the id in the bees table.

That match is the connection. It's the flight path between the two. A bee can fly it in either direction.

The column that carries that match is called a foreign key — it holds a value that identifies a row in a different table. Here, pollinated_by is a foreign key that identifies a bee by its id.

The same connection, shown as a moment in the garden:

Each arrow says "the bee that pollinated this flower is the row over there with that id." When you write a JOIN query, what you're really telling SQL is which arrows to follow.

You might be wondering: why not just put the bee's name directly into the flowers table? Something like:

Honey appears twice. The day the gardener decides to rename her — "Queen Honey", let's say — you have to find every row that says "Honey" and edit it. Miss one, and the garden disagrees with itself.

With the two-table version, Honey lives in exactly one row in bees. Change it once and every flower that points at her updates by association.

This is the heart of why databases use multiple tables: each fact gets stored exactly once, in the place it belongs, and pointers connect them.

An INNER JOIN is the basic flavor. The bee flies the path between the bees and the flowers. For each bee, it looks across at the flowers that bee pollinated — and puts the pair into the basket. If there's no match on either side, the row is dropped.

Press Run query. Watch the ribbons connect each flower to its bee, and the basket fill with three pairs.

Every JOIN has an ON clause. It's the rule the bee uses to decide which flower on one side pairs with which bee on the other.

Here we say: ON flowers.pollinated_by = bees.id. In plain English: "pair a flower with the bee whose id equals that flower's pollinated_by."

Try changing the ON to something nonsensical — say, ON bees.id = 999 — and watch every ribbon disappear. The bee has no rule that matches, so nothing pairs.

Bumble hasn't visited a flower. An INNER JOIN drops her from the basket because there's no pair to make. But what if you want Bumble in the basket anyway — perhaps to know which bees haven't pollinated anything yet?

A LEFT JOIN keeps every row from the left-side table (bees), even those with no match on the right. Left means the table in your FROM; right means the table after JOIN. The unmatched ones get a NULL on the right — the garden word for "nothing here."

Now flip the situation. You want every flower in the basket, even those without a bee. The Tulip, waiting unpollinated, should be there too. Use a RIGHT JOIN:

What if you want everyone — every bee and every flower, paired up where possible and standing alone where not? That's a FULL OUTER JOIN.

The basket comes home with five rows: three real pairs, plus Bumble (no flower) and the Tulip (no bee), each with a NULL on the missing side.

You've now seen all four common flavors. The only difference between them is who gets left in the bed when there's no pair on the other side.

A note on portability: not every database speaks all four. MySQL has no FULL OUTER JOIN at all, and SQLite only gained RIGHT and FULL joins in version 3.39 (2022). Where they're missing, you can emulate FULL as a LEFT JOIN unioned with a RIGHT JOIN (or two LEFT JOINs with the tables flipped).

The two tables are still there. Try any JOIN you can think of. The result table below shows the rows the basket comes home with.

To find rows where a joined column is blank, use IS NULL, not = NULL. NULL isn't a value that equals anything — including itself — so = NULL never matches. IS NULL is a predicate that asks about the value's presence, not its equality.

In the garden of the Flight Path, two beds are walked at once. One holds the bees; the other holds the flowers. The gardener pairs them four different ways — by pairs only, by every bee, by every flower, and by everyone at once.

After this garden lies the second of Summer's two: it is called the Sorting Table. There a fresh basket of flowers is set down, and its contents are placed into smaller baskets — one color to each — and from each smaller basket a number is taken. For today, the pairs have been made; nobody is left behind; the path quiets, and the sun starts down.