SQL Lab: joins¶

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

La generació 5 té 156 Pokémon. Els vas comptar tu mateix a la lliçó anterior. Ara: quina regió és la generació 5? Quin joc la va introduir? En quin any?

Un fan et respondrà sense pestanyejar: Unova, Black and White, 2010. La taula pokemon no pot. Les seves cartes només porten un número, 5. El que aquest número vol dir està escrit en un altre lloc, en una altra taula. Les bases de dades reals en van plenes: una taula diu «el client 4412 ha comprat alguna cosa», una altra diu qui és el 4412. Aquesta lliçó va de llegir dues taules com si fossin una, i de les files que desapareixen en silenci quan ho fas.

Una segona capsa: generations¶

Mantenim la capsa de sabates de les dues últimes lliçons: pokemon és una capsa de 949 cartes col·leccionables. Avui apareix sobre la taula una segona capsa, minúscula: vuit cartes de regió.

Descarrega generations.csv de la pàgina Recursos i carrega'l de la mateixa manera que vas carregar pokemon:

SELECT * FROM generations ORDER BY generation_id;

Aquí tens la resposta a la pregunta inicial, a la fila 5. Aquest tipus de taula petita que explica un codi s'anomena taula de cerca (lookup table).

La clau: la línia impresa a les dues cartes¶

Una clau és una columna que dues taules comparteixen i que et diu quina fila d'una taula pertany a quina fila de l'altra.

Mira les dues capses. Quina línia és impresa a les cartes de totes dues? generation_id. És l'únic que connecta una carta de Pokémon amb la seva carta de regió.

flowchart LR
    subgraph P ["<b>pokemon</b> · 949 cartes"]
        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 = en blanc"]
    end
    subgraph G ["<b>generations</b> · 8 cartes"]
        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

Tres coses per notar al dibuix:

• Una carta de regió, molts Pokémon. Només Kanto està enllaçada amb 151 Pokémon.
• charizard-mega-x té el generation_id en blanc. No en surt cap línia.
• A Galar no hi arriba cap línia. El fitxer de Pokémon s'atura a la generació 7.

Guarda't les dues últimes. Són la trama d'aquesta lliçó.

INNER JOIN: enganxa les cartes amb un clip¶

Un join combina les files de dues taules en un sol resultat, aparellant-les per una condició. Un INNER JOIN conserva només les files que han trobat parella.

Imagina un clip. Un join enganxa cada carta de Pokémon amb la carta de regió que té el mateix generation_id. El resultat és una carta ampla amb les línies de totes dues.

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;

Dues columnes d'una capsa, dues de l'altra, a la mateixa fila. Tres peces de gramàtica nova:

Les files que desapareixen¶

pokemon té 949 files. Abans d'executar la consulta següent, endevina: quantes files té el join?

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

802. No 949. Cap error, cap avís. Han desaparegut 147 cartes.

Ja les coneixes. A la lliçó anterior, en agrupar per generation_id va sortir un munt amb la clau en blanc i exactament 147 cartes. Un blanc no és mai igual a res; recorda el = NULL de SQL Lab: filtres. Així que aquestes 147 cartes no troben parella, i un INNER JOIN deixa sobre la taula, en silenci, una carta sense parella.

LEFT JOIN: conserva totes les cartes de la capsa de l'esquerra¶

Un LEFT JOIN conserva totes les files de la taula de l'esquerra, la que s'escriu primer, després de FROM. Una fila sense parella obté NULL a totes les columnes de l'altra taula.

Canvia una paraula, INNER per LEFT, i torna a comptar:

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

949. Tothom ha tornat.

Un sol Pokémon mostra tota la diferència. Cerca charizard% amb un 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;

Tres files, dues amb la regió en blanc. Executa la mateixa consulta amb INNER JOIN i en obtens una: el charizard normal. Els dos Charizard Mega s'esvaeixen.

Trobar els desapareguts¶

Aquest blanc és útil. Un LEFT JOIN seguit de «on l'altre costat és en blanc» llista exactament les files que un INNER JOIN descartaria:

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 files. Les primeres: deoxys-attack, deoxys-defense, deoxys-speed, wormadam-sandy, wormadam-trash, shaymin-sky... Llegeix la llista cap avall i apareix un patró: tots són formes alternatives d'un Pokémon que ja existeix. 48 són evolucions Mega, 12 són Pikachu amb disfressa, d'altres són formes d'Alola. En aquest fitxer, les formes alternatives no tenen generació.

Quina taula és a l'esquerra?¶

«Esquerra» vol dir simplement escrita primer. Així que tries quina capsa es conserva sencera. Gira el join:

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 apareix per primera vegada, amb 0 Pokémon. Era la carta de regió a la qual cap carta de Pokémon apuntava.

Aquesta consulta també mostra que un join i GROUP BY treballen junts: primer s'enganxen les cartes, després les cartes amples es reparteixen en munts per regió.

Tres recomptes, una sola taula¶

Les mateixes dues taules, la mateixa clau, tres resultats diferents:

I l'elecció canvia respostes, no només recomptes. Quin és el Pokémon més fort, i de quina regió?

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;

Les mateixes dades, la mateixa pregunta, dues respostes. Les evolucions Mega no tenen generació en aquest fitxer, així que l'INNER JOIN va llençar les cartes més fortes de la capsa. Cap join és «el correcte». Tries tu, i deixes escrit què has triat.

Prova-ho: les files que desapareixen¶

A SQL Lab, amb els àlies p i g i sempre amb ON p.generation_id = g.generation_id. Per a cada join, anota quantes files en surten.

Si un join retorna 7592 files, la condició ON és sempre certa, o bé les taules s'han unit amb una coma en lloc d'un JOIN: cada Pokémon s'ha enganxat a cada regió (949 × 8).

Idees clau¶

• Una clau és la columna que dues taules comparteixen. Aquí és generation_id: una carta de regió, moltes cartes de Pokémon.
• Un join enganxa files que coincideixen en una sola fila ampla. ON diu quines columnes han de ser iguals; els àlies de taula (p, g) diuen de quina taula és la columna.
• Un INNER JOIN conserva només les parelles enganxades: 802 files. Una clau en blanc no coincideix mai, així que 147 Pokémon desapareixen sense cap error.
• Un LEFT JOIN conserva totes les files de la taula escrita primer: 949 files, amb NULL on no hi ha parella.
• LEFT JOIN ... WHERE g.region IS NULL llista les files que un INNER JOIN descarta.
• Després de cada join, compta les files i compara-les amb el recompte d'abans. El join que tries canvia la resposta.

Comprovació ràpida¶

Avui has escrit unes quantes bones consultes. On seran demà? Tot seguit: Datasets i destí, on desaràs la pregunta i guardaràs la resposta.