RIGHT JOIN, FULL OUTER JOIN y CROSS JOIN

RIGHT JOIN: conservar la tabla derecha

El LEFT JOIN conserva todas las filas de la izquierda. Su simétrico, el RIGHT JOIN (o RIGHT OUTER JOIN), conserva todas las filas de la derecha (la que va después del JOIN), tengan o no pareja a la izquierda. Cuando no hay coincidencia, las columnas de la tabla izquierda quedan a NULL.

SELECT productos.nombre, categorias.nombre
FROM productos
RIGHT JOIN categorias ON productos.categoria_id = categorias.id;

Aquí la derecha es categorias, así que aparecen todas las categorías, incluso las que no tienen ningún producto (con NULL en la columna del producto). Fíjate en que A RIGHT JOIN B da el mismo resultado que B LEFT JOIN A: son dos formas de escribir lo mismo. Por eso en la práctica suele preferirse el LEFT JOIN, reordenando las tablas.

FULL OUTER JOIN: conservar ambos lados

El FULL OUTER JOIN combina los dos: conserva todas las filas de ambas tablas. Las que tienen pareja se emparejan; las que no, aparecen igualmente con NULL en el lado que falta.

SELECT productos.nombre, categorias.nombre
FROM productos
FULL OUTER JOIN categorias ON productos.categoria_id = categorias.id;

En el resultado aparecen tanto los productos sin categoría (con NULL en el nombre de la categoría) como las categorías sin productos (con NULL en el nombre del producto). Es la unión de lo que devolverían un LEFT JOIN y un RIGHT JOIN.

Nota de compatibilidad: SQLite solo admite RIGHT JOIN y FULL OUTER JOIN desde la versión 3.39 (2022). En versiones más antiguas tendrías que simularlos reordenando tablas (LEFT JOIN) o con UNION.

CROSS JOIN: el producto cartesiano

El CROSS JOIN no lleva ON: combina cada fila de la izquierda con cada fila de la derecha. Es el producto cartesiano. Si A tiene 3 filas y B tiene 4, el resultado tiene 3 × 4 = 12 filas.

-- Todas las combinaciones de talla y color.
SELECT tallas.valor, colores.valor
FROM tallas
CROSS JOIN colores;

Sirve para generar todas las combinaciones posibles (tallas × colores, días × horas...). Hay que usarlo con cuidado: el número de filas crece muy rápido. De hecho, un INNER JOIN no es más que un CROSS JOIN al que luego se le aplica la condición del ON como filtro.

Ejemplos

FULL OUTER JOIN: productos y categorías, falte lo que falte

SELECT productos.nombre AS producto, categorias.nombre AS categoria
FROM productos
FULL OUTER JOIN categorias ON productos.categoria_id = categorias.id
ORDER BY categoria, producto;

CROSS JOIN: todas las combinaciones de talla y color

SELECT t.valor AS talla, c.valor AS color
FROM tallas AS t
CROSS JOIN colores AS c
ORDER BY t.valor, c.valor;