JOINs en SQL

Explicando a un amigo cómo funcionan los JOIN en SQL [1] me confundí por un momento con otra cosa y le dije puras falsedades hasta que reaccioné y me di cuenta de que estaba confundido, o —como diría Les Luthiers— reflexionando por caminos sinuosos, digamos, razonando fuera del recipiente.

Además, para una persona que no ha manejado JOINs antes, o que los ha manejado pero tiene dudas, es mejor mostrar un ejemplo. He optado por preparar uno y dejarlo aquí fines de apoyo didáctico.

Tenemos dos tablas: viviendas y colores. Para la vivienda almacenamos el nombre del dueño y el color. Para la tabla de colores almacenamos el color y su fórmula en formato hexadecimal. Después tenemos los cuatro tipos principales de JOIN. ¿Qué diferencia hay entre los cuatro?

alvarezp=# CREATE TABLE viviendas (dueno varchar PRIMARY KEY, color varchar);
CREATE TABLE
alvarezp=# INSERT INTO viviendas VALUES ('lucho',  'verde');
INSERT 0 1
alvarezp=# INSERT INTO viviendas VALUES ('chuy',   'azul' );
INSERT 0 1
alvarezp=# INSERT INTO viviendas VALUES ('pancho',  NULL  );
INSERT 0 1

alvarezp=# CREATE TABLE colores (nombre varchar PRIMARY KEY, hexval varchar);
CREATE TABLE
alvarezp=# INSERT INTO colores VALUES ('verde', '#00ff00');
INSERT 0 1
alvarezp=# INSERT INTO colores VALUES ('azul',  '#0000ff');
INSERT 0 1
alvarezp=# INSERT INTO colores VALUES ('rojo',  '#ff0000');
INSERT 0 1

alvarezp=# SELECT * FROM viviendas INNER JOIN colores ON viviendas.color = colores.nombre;
 dueno | color | nombre | hexval  
-------+-------+--------+---------
 lucho | verde | verde  | #00ff00
 chuy  | azul  | azul   | #0000ff
(2 rows)

alvarezp=# SELECT * FROM viviendas FULL OUTER JOIN colores ON viviendas.color = colores.nombre;
 dueno  | color | nombre | hexval  
--------+-------+--------+---------
 lucho  | verde | verde  | #00ff00
 chuy   | azul  | azul   | #0000ff
 pancho |       |        | 
        |       | rojo   | #ff0000
(4 rows)

alvarezp=# SELECT * FROM viviendas LEFT OUTER JOIN colores ON viviendas.color = colores.nombre;
 dueno  | color | nombre | hexval  
--------+-------+--------+---------
 lucho  | verde | verde  | #00ff00
 chuy   | azul  | azul   | #0000ff
 pancho |       |        | 
(3 rows)

alvarezp=# SELECT * FROM viviendas RIGHT OUTER JOIN colores ON viviendas.color = colores.nombre;
 dueno | color | nombre | hexval  
-------+-------+--------+---------
 lucho | verde | verde  | #00ff00
 chuy  | azul  | azul   | #0000ff
       |       | rojo   | #ff0000
(3 rows)

alvarezp=# SELECT * FROM viviendas CROSS JOIN colores;
 dueno  | color | nombre | hexval  
--------+-------+--------+---------
 lucho  | verde | verde  | #00ff00
 lucho  | verde | azul   | #0000ff
 lucho  | verde | rojo   | #ff0000
 chuy   | azul  | verde  | #00ff00
 chuy   | azul  | azul   | #0000ff
 chuy   | azul  | rojo   | #ff0000
 pancho |       | verde  | #00ff00
 pancho |       | azul   | #0000ff
 pancho |       | rojo   | #ff0000
(9 rows)

El CROSS JOIN es el más fácil de explicar: es la relación de cada registro de una tabla con cada registro de otra. Si hay 23 registros en la primera tabla y 19 en la segunda tabla, el CROSS JOIN arrojará 437 resultados.

INNER JOIN muestra sólo aquellos registro que tienen un registro relacionado en ambas tablas y los datos se unen a través de los campo de la cláusula ON. Se representa como la intersección de ambos conjuntos. Es equivalente de aplicar la cláusula ON como si fuera un WHERE después de un CROSS JOIN. Así es como se hacían los INNER JOIN antes de SQL-92.

OUTER JOIN muestra todos los registros de una tabla y los relacionados con la otra tabla. Existen tres tipos de OUTER JOIN, según el la tabla que incluye la totalidad de registros:

LEFT OUTER JOIN, muestra todos los registros de la tabla mencionada primero en el FROM y sólo los registros relacionados de la tabla mencionada segundo.

RIGHT OUTER JOIN, muestra todos los registros de la tabla mencionada segundo en el FROM y sólo los registros relacionados de la tabla mencionada segundo.

FULL OUTER JOIN, muestra todos los registros de ambas tablas, independientemente de que no tengan registro relacionado en la otra.

Para cualquier duda, favor de usar los comentarios.

[1] JOIN y UNION no son lo mismo. JOIN es para unir (horizontalmente) tablas según un valor en común y UNION es para unir (verticalmente) resultados de consultas con los mismos atributos. Cuidado, que la traducción al español está relacionada con unir en ambos casos.


Comentarios

JOINs en SQL — 4 comentarios

  1. Un pequeño error ortográfico: «registri».

    Muy buena explicación de lo que son los JOINs. Yo, en las broncas en las que me he metido con los JOINs es en relación al desempeño.

    Cuando haces ejercicios con pocos campos es una cosa. Pero sería bueno generar una tabla de unos 20 millones de registros y ver qué conviene más.

    También, está el EXPLAIN; que ayuda mucho en estos casos, no?

    En la chamba, he visto que mucho del show para el desempeño está relacionado con la correcta indexación de los campos; lo cual está completamente fuera de contexto aquí; pero tenía que mencionarlo.

    Muy buen artículo, mi alvarezp!

    • Corregido el typo. ¡Muchísimas gracias!

      Generé dos tablas de 10 millones de registros cada una en mi laptop con 8GB de RAM.

      * El INNER JOIN lo hizo en 12 segundos sin WHERE y con campos indizados.

      * Si le agregas un WHERE para buscar un rango de registros, se tarda sólo 125 ms.

      Tres puntos importantes:

      * Primero, el CROSS JOIN nunca terminó; me hubiera dado 10M * 10M = 100T combinaciones; no 100G sino 100T.

      * Segundo, el tamaño de las tablas y los índices suma 1.2 GB, según pg_stats.

      * Tercero, aunque el disco duro es un SSD, casi estoy seguro de que toda la DB está corriendo en RAM gracias al caché de disco.

      Realmente no es el JOIN lo tardado sino la complejidad de los datos que necesites. El planner va a optimizar la ejecución según lo necesites. Por ejemplo:

      SELECT * FROM lt1 LEFT OUTER JOIN lt2 USING(id) tarda sus 12 segundos; en cambio:

      SELECT lt1.id FROM lt1 LEFT OUTER JOIN lt2 USING(id) tardó sólo 1.5 segundos! ¿Por qué? Porque PostgreSQL piensa: «si no me piden datos de lt2, no tiene caso hacer el JOIN».

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *