SQL Lab: joins¶

Exercise sql-lab-joins-annex.pdf · PDF

Generation 5 has 156 Pokémon. You counted them yourself in the last lesson. Now: which region is generation 5? Which game introduced it? In which year?

A fan will answer without blinking: Unova, Black and White, 2010. The table pokemon cannot. Its cards only carry a number, 5. What that number means is written somewhere else, in another table. Real databases are full of this: one table says "customer 4412 bought something", another says who 4412 is. This lesson is about reading two tables as one — and about the rows that quietly disappear when you do.

A second box: generations¶

We keep the shoebox from the last two lessons: pokemon is a box of 949 trading cards. Today a second, tiny box appears on the table: eight region cards.

Download generations.csv from the Resources page and load it the same way you loaded pokemon:

SELECT * FROM generations ORDER BY generation_id;

There is the answer to the opening question, in row 5. This kind of small table that explains a code is called a lookup table.

The key: the line printed on both cards¶

A key is a column that two tables share, and that tells you which row of one table belongs to which row of the other.

Look at both boxes. Which line is printed on the cards of both? generation_id. It is the only thing that connects a Pokémon card to its region card.

flowchart LR
    subgraph P ["<b>pokemon</b> · 949 cards"]
        direction TB
        P1["bulbasaur<br>generation_id = 1"]
        P2["pikachu<br>generation_id = 1"]
        P3["lucario<br>generation_id = 4"]
        P4["charizard-mega-x<br>generation_id = blank"]
    end
    subgraph G ["<b>generations</b> · 8 cards"]
        direction TB
        G1["1 · Kanto · 1996"]
        G4["4 · Sinnoh · 2006"]
        G8["8 · Galar · 2019"]
    end
    P1 -- "1 = 1" --> G1
    P2 -- "1 = 1" --> G1
    P3 -- "4 = 4" --> G4

Three things to notice in the drawing:

• One region card, many Pokémon cards. Kanto alone is linked to 151 Pokémon.
• charizard-mega-x has a blank generation_id. No line leaves it.
• Galar has no line arriving. The Pokémon file stops at generation 7.

Hold on to the last two. They are the plot of this lesson.

INNER JOIN: clip the cards together¶

A join combines the rows of two tables into one result, matching them by a condition. An INNER JOIN keeps only the rows that found a partner.

Picture a paper clip. A join clips each Pokémon card to the region card with the same generation_id. The result is one wide card with the lines of both.

SELECT p.pokemon, p.type_1, g.region, g.release_year
FROM pokemon AS p
INNER JOIN generations AS g ON p.generation_id = g.generation_id
ORDER BY p.id;

Two columns from one box, two from the other, in the same row. Three pieces of new grammar:

The rows that vanish¶

pokemon has 949 rows. Before you run the next query, guess: how many rows does the join have?

SELECT COUNT(*)
FROM pokemon AS p
INNER JOIN generations AS g ON p.generation_id = g.generation_id;

802. Not 949. No error, no warning. 147 cards are gone.

You have met them before. In the last lesson, grouping by generation_id produced a pile with a blank key and exactly 147 cards. A blank is never equal to anything — remember = NULL from SQL Lab: filters. So those 147 cards find no partner, and an INNER JOIN leaves a card with no partner on the table, silently.

LEFT JOIN: keep every card of the left box¶

A LEFT JOIN keeps every row of the left table — the one written first, after FROM. A row with no partner gets NULL in every column of the other table.

Change one word, INNER to LEFT, and count again:

SELECT COUNT(*)
FROM pokemon AS p
LEFT JOIN generations AS g ON p.generation_id = g.generation_id;

949. Everybody is back.

One Pokémon shows the whole difference. Search charizard% with a LEFT JOIN:

SELECT p.pokemon, g.region
FROM pokemon AS p
LEFT JOIN generations AS g ON p.generation_id = g.generation_id
WHERE p.pokemon LIKE 'charizard%'
ORDER BY p.id;

Three rows, two of them with a blank region. Run the same query with INNER JOIN and you get one row: plain charizard. The two Mega Charizards vanish.

Finding the vanished¶

That blank is useful. A LEFT JOIN followed by "where the other side is blank" lists exactly the rows that an INNER JOIN would drop:

SELECT p.pokemon, p.generation_id, g.region
FROM pokemon AS p
LEFT JOIN generations AS g ON p.generation_id = g.generation_id
WHERE g.region IS NULL
ORDER BY p.id;

147 rows. The first ones: deoxys-attack, deoxys-defense, deoxys-speed, wormadam-sandy, wormadam-trash, shaymin-sky... Read down the list and a pattern appears: they are all alternate forms of a Pokémon that already exists. 48 of them are Mega evolutions, 12 are costume Pikachus, others are Alola forms. In this file, alternate forms have no generation.

Which table is on the left?¶

"Left" just means written first. So you choose which box is kept whole. Turn the join around:

SELECT g.region, COUNT(p.id) AS n
FROM generations AS g
LEFT JOIN pokemon AS p ON p.generation_id = g.generation_id
GROUP BY g.region
ORDER BY n DESC;

Galar appears for the first time, with 0 Pokémon. It was the region card that no Pokémon card pointed at.

This query also shows that a join and GROUP BY work together: first the cards are clipped, then the wide cards are dealt into piles by region.

Three counts, one table¶

Same two tables, same key, three different results:

And the choice changes answers, not only counts. Who is the strongest Pokémon, and from which region?

SELECT p.pokemon, p.attack, g.region
FROM pokemon AS p
INNER JOIN generations AS g ON p.generation_id = g.generation_id
ORDER BY p.attack DESC
LIMIT 3;

SELECT p.pokemon, p.attack, g.region
FROM pokemon AS p
LEFT JOIN generations AS g ON p.generation_id = g.generation_id
ORDER BY p.attack DESC
LIMIT 3;

Same data, same question, two answers. The Mega evolutions have no generation in this file, so the INNER JOIN threw away the strongest cards in the box. Neither join is "the correct one". You choose, and you write down what you chose.

Try it: the rows that vanish¶

In SQL Lab, with the aliases p and g and always ON p.generation_id = g.generation_id. For each join, write down how many rows come out.

If a join returns 7592 rows, the ON condition is always true, or the tables were joined with a comma instead of a JOIN: every Pokémon got clipped to every region (949 × 8).

Key takeaways¶

• A key is the column two tables share. Here it is generation_id: one region card, many Pokémon cards.
• A join clips matching rows into one wide row. ON says which columns must be equal; table aliases (p, g) say whose column you mean.
• An INNER JOIN keeps only clipped pairs: 802 rows. A blank key never matches, so 147 Pokémon vanish without an error.
• A LEFT JOIN keeps every row of the table written first: 949 rows, with NULL where there is no partner.
• LEFT JOIN ... WHERE g.region IS NULL lists the rows an INNER JOIN drops.
• After every join, count the rows and compare with the count before. The join you choose changes the answer.

Quick check¶

You wrote some good queries today. Where will they be tomorrow? Next: Datasets and destination, where you save the question and store the answer.