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.