SQL Lab: joins¶

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

La generación 5 tiene 156 Pokémon. Los contaste tú mismo en la lección anterior. Ahora: ¿qué región es la generación 5? ¿Qué juego la introdujo? ¿En qué año?

Un fan responderá sin pestañear: Unova, Black and White, 2010. La tabla pokemon no puede. Sus cartas solo llevan un número, 5. Lo que ese número significa está escrito en otro sitio, en otra tabla. Las bases de datos reales están llenas de esto: una tabla dice "el cliente 4412 compró algo", otra dice quién es el 4412. Esta lección trata de leer dos tablas como una, y de las filas que desaparecen en silencio cuando lo haces.

Una segunda caja: generations¶

Mantenemos la caja de zapatos de las dos últimas lecciones: pokemon es una caja con 949 cartas coleccionables. Hoy aparece sobre la mesa una segunda caja, diminuta: ocho cartas de región.

Descarga generations.csv desde la página Recursos y cárgalo igual que cargaste pokemon:

SELECT * FROM generations ORDER BY generation_id;

Ahí está la respuesta a la pregunta del principio, en la fila 5. Este tipo de tabla pequeña que explica un código se llama tabla de referencia (lookup table).

La clave: la línea impresa en las dos cartas¶

Una clave es una columna que dos tablas comparten y que te dice qué fila de una tabla pertenece a qué fila de la otra.

Mira las dos cajas. ¿Qué línea está impresa en las cartas de las dos? generation_id. Es lo único que conecta una carta de Pokémon con su carta de región.

flowchart LR
    subgraph P ["<b>pokemon</b> · 949 cartas"]
        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 blanco"]
    end
    subgraph G ["<b>generations</b> · 8 cartas"]
        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 cosas que notar en el dibujo:

• Una carta de región, muchas cartas de Pokémon. Solo Kanto está unida a 151 Pokémon.
• charizard-mega-x tiene el generation_id en blanco. No sale ninguna línea de él.
• A Galar no llega ninguna línea. El archivo de Pokémon se detiene en la generación 7.

Quédate con las dos últimas. Son el argumento de esta lección.

INNER JOIN: une las cartas con un clip¶

Un join combina las filas de dos tablas en un solo resultado, emparejándolas con una condición. Un INNER JOIN se queda solo con las filas que han encontrado pareja.

Imagina un clip. Un join sujeta cada carta de Pokémon con la carta de región que tiene el mismo generation_id. El resultado es una carta ancha con las líneas de las dos.

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;

Dos columnas de una caja, dos de la otra, en la misma fila. Tres piezas de gramática nueva:

Las filas que desaparecen¶

pokemon tiene 949 filas. Antes de ejecutar la siguiente consulta, adivina: ¿cuántas filas tiene el join?

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

802. No 949. Ningún error, ningún aviso. Han desaparecido 147 cartas.

Ya las conoces. En la lección anterior, agrupar por generation_id produjo un montón con la clave en blanco y exactamente 147 cartas. Un blanco nunca es igual a nada (recuerda = NULL de SQL Lab: filtros). Así que esas 147 cartas no encuentran pareja, y un INNER JOIN deja sobre la mesa, en silencio, una carta sin pareja.

LEFT JOIN: conserva todas las cartas de la caja izquierda¶

Un LEFT JOIN conserva todas las filas de la tabla izquierda, la que se escribe primero, después de FROM. Una fila sin pareja recibe NULL en todas las columnas de la otra tabla.

Cambia una palabra, INNER por LEFT, y cuenta otra vez:

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

949. Todos han vuelto.

Un Pokémon muestra toda la diferencia. Busca charizard% con 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 filas, dos de ellas con la región en blanco. Ejecuta la misma consulta con INNER JOIN y obtienes una fila: el charizard normal. Los dos Charizard Mega se esfuman.

Encontrar a los desaparecidos¶

Ese blanco es útil. Un LEFT JOIN seguido de "donde el otro lado está en blanco" lista exactamente las filas que un INNER JOIN descartaría:

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 filas. Las primeras: deoxys-attack, deoxys-defense, deoxys-speed, wormadam-sandy, wormadam-trash, shaymin-sky... Lee la lista hacia abajo y aparece un patrón: todas son formas alternativas de un Pokémon que ya existe. 48 de ellas son evoluciones Mega, 12 son Pikachu con disfraz, otras son formas de Alola. En este archivo, las formas alternativas no tienen generación.

¿Qué tabla está a la izquierda?¶

"Izquierda" solo significa escrita primero. Así que tú eliges qué caja se conserva entera. Da la vuelta al 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 aparece por primera vez, con 0 Pokémon. Era la carta de región a la que no apuntaba ninguna carta de Pokémon.

Esta consulta también muestra que un join y GROUP BY trabajan juntos: primero se unen las cartas con el clip, luego las cartas anchas se reparten en montones por región.

Tres recuentos, una tabla¶

Las mismas dos tablas, la misma clave, tres resultados distintos:

Y la elección cambia las respuestas, no solo los recuentos. ¿Quién es el Pokémon más fuerte, y de qué región?

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;

Los mismos datos, la misma pregunta, dos respuestas. Las evoluciones Mega no tienen generación en este archivo, así que el INNER JOIN tiró las cartas más fuertes de la caja. Ningún join es "el correcto". Tú eliges, y apuntas lo que has elegido.

Pruébalo: las filas que desaparecen¶

En SQL Lab, con los alias p y g y siempre ON p.generation_id = g.generation_id. Para cada join, apunta cuántas filas salen.

Si un join devuelve 7592 filas, la condición ON es siempre verdadera, o las tablas se unieron con una coma en lugar de con un JOIN: cada Pokémon se sujetó con el clip a cada región (949 × 8).

Ideas clave¶

• Una clave es la columna que comparten dos tablas. Aquí es generation_id: una carta de región, muchas cartas de Pokémon.
• Un join une con el clip filas que coinciden en una fila ancha. ON dice qué columnas deben ser iguales; los alias de tabla (p, g) dicen de quién es la columna.
• Un INNER JOIN conserva solo las parejas unidas: 802 filas. Una clave en blanco nunca coincide, así que 147 Pokémon desaparecen sin error.
• Un LEFT JOIN conserva todas las filas de la tabla escrita primero: 949 filas, con NULL donde no hay pareja.
• LEFT JOIN ... WHERE g.region IS NULL lista las filas que descarta un INNER JOIN.
• Después de cada join, cuenta las filas y compáralas con el recuento de antes. El join que elijas cambia la respuesta.

Comprobación rápida¶

Hoy has escrito buenas consultas. ¿Dónde estarán mañana? Siguiente: Datasets y destino, donde guardas la pregunta y almacenas la respuesta.