Tipos de JOIN en SQL

Imagina que tienes esta base de datos

Supongamos que estamos desarrollando el sistema de una tienda en línea. Tenemos clientes que realizan pedidos y cada pedido pertenece a un cliente.

La base de datos tiene estas dos tablas:

clientes
--------------------------------
id | nombre
1  | Arturo
2  | María
3  | Carlos
4  | Sofía

pedidos
--------------------------------
id | cliente_id | producto       | total
1  | 1          | Teclado        | 1200
2  | 1          | Mouse          | 500
3  | 2          | Monitor        | 4500
4  | 3          | Audífonos      | 900

La relación está en pedidos.cliente_id, que indica a qué cliente pertenece cada pedido.

Por ejemplo:

pedidos.cliente_id = clientes.id

El objetivo ahora es consultar información de las dos tablas al mismo tiempo.

Ahí aparecen los JOIN.

1. ¿Qué demonios hace un JOIN?

Un JOIN combina registros de dos o más tablas utilizando una condición que establece cómo se relacionan.

La idea más básica es esta:

clientes + pedidos
       ->
      JOIN
       ->
información combinada

Por ejemplo, queremos saber qué cliente realizó cada pedido:

SELECT clientes.nombre, pedidos.producto, pedidos.total
FROM clientes
JOIN pedidos
  ON clientes.id = pedidos.cliente_id;

El resultado sería:

Arturo | Teclado   | 1200
Arturo | Mouse     | 500
María  | Monitor   | 4500
Carlos | Audífonos | 900

Aquí el JOIN encontró los registros donde clientes.id coincide con pedidos.cliente_id.

2. INNER JOIN

El INNER JOIN devuelve únicamente los registros que tienen coincidencia en ambas tablas.

SELECT clientes.nombre, pedidos.producto, pedidos.total
FROM clientes
INNER JOIN pedidos
  ON clientes.id = pedidos.cliente_id;

Con nuestros datos, Arturo, María y Carlos aparecen porque tienen pedidos.

Sofía no aparece porque no existe ningún registro en pedidos con cliente_id = 4.

Podemos visualizarlo así:

clientes             pedidos
   [coinciden]  <->  [coinciden]
            |
            v
      solo coincidencias

El punto importante es que un INNER JOIN descarta los registros que no tienen una coincidencia.

3. LEFT JOIN

Ahora supongamos que queremos mostrar todos los clientes, incluso aquellos que todavía no han realizado ningún pedido.

Para eso usamos LEFT JOIN.

SELECT clientes.nombre, pedidos.producto, pedidos.total
FROM clientes
LEFT JOIN pedidos
  ON clientes.id = pedidos.cliente_id;

El resultado sería conceptualmente:

Arturo | Teclado   | 1200
Arturo | Mouse     | 500
María  | Monitor   | 4500
Carlos | Audífonos | 900
Sofía  | NULL      | NULL

Aquí está la diferencia importante:

LEFT JOIN conserva todos los registros de la tabla que está a la izquierda, aunque no exista coincidencia en la tabla de la derecha.

En este caso, clientes está a la izquierda:

clientes
   |
   | LEFT JOIN
   v
pedidos

Por eso Sofía aparece aunque no tenga pedidos.

4. RIGHT JOIN

El RIGHT JOIN funciona de forma inversa. Conserva todos los registros de la tabla que está a la derecha y agrega la información coincidente de la tabla izquierda.

SELECT clientes.nombre, pedidos.producto, pedidos.total
FROM clientes
RIGHT JOIN pedidos
  ON clientes.id = pedidos.cliente_id;

Con nuestros datos, todos los pedidos aparecen porque cada pedido tiene un cliente_id que corresponde a un cliente.

El resultado sería:

Arturo | Teclado   | 1200
Arturo | Mouse     | 500
María  | Monitor   | 4500
Carlos | Audífonos | 900

En la práctica, un RIGHT JOIN puede expresarse muchas veces reorganizando las tablas y utilizando un LEFT JOIN. Por eso LEFT JOIN suele aparecer con mayor frecuencia en consultas.

5. FULL OUTER JOIN

El FULL OUTER JOIN conserva los registros de ambas tablas, tengan o no coincidencia.

La idea es:

TODOS los clientes
        +
TODOS los pedidos
        +
coincidencias entre ambos

La consulta sería:

SELECT clientes.nombre, pedidos.producto, pedidos.total
FROM clientes
FULL OUTER JOIN pedidos
  ON clientes.id = pedidos.cliente_id;

Con los datos actuales, obtendríamos los clientes con sus pedidos y también cualquier registro que existiera en una de las tablas sin correspondencia en la otra.

Por ejemplo, si agregáramos un pedido con cliente_id = 99, el resultado tendría una fila similar a:

NULL | Webcam | 1800

El NULL aparece porque existe el pedido, pero no existe un cliente correspondiente.

De la misma forma, Sofía aparecería con sus columnas de pedido en NULL porque existe el cliente, pero no existe un pedido relacionado.

6. CROSS JOIN

El CROSS JOIN es diferente de los anteriores. No necesita una condición ON para relacionar las tablas.

Genera el producto cartesiano: cada fila de una tabla se combina con cada fila de la otra.

SELECT clientes.nombre, pedidos.producto
FROM clientes
CROSS JOIN pedidos;

Tenemos 4 clientes y 4 pedidos, así que el resultado tendría:

4 x 4 = 16 filas

Podríamos obtener combinaciones como:

Arturo | Teclado
Arturo | Mouse
Arturo | Monitor
Arturo | Audífonos
María  | Teclado
María  | Mouse
...

Aquí no estamos preguntando qué pedido pertenece a qué cliente. Estamos generando todas las combinaciones posibles entre ambos conjuntos de registros.

Por eso un CROSS JOIN debe usarse con una intención clara. Con tablas grandes, el número de combinaciones puede crecer rápidamente.

7. SELF JOIN

Un SELF JOIN ocurre cuando una tabla se relaciona consigo misma.

Vamos a extender nuestro único caso real. Supongamos que la tabla clientes también tiene un campo referido_por que almacena el id de otro cliente.

Podríamos tener:

clientes
--------------------------------
id | nombre | referido_por
1  | Arturo | NULL
2  | María  | 1
3  | Carlos | 1
4  | Sofía  | 2

Ahora queremos saber quién refirió a cada cliente.

Podemos utilizar la misma tabla dos veces mediante alias:

SELECT
  cliente.nombre AS cliente,
  referente.nombre AS referente
FROM clientes AS cliente
LEFT JOIN clientes AS referente
  ON cliente.referido_por = referente.id;

El resultado sería:

Arturo | NULL
María  | Arturo
Carlos | Arturo
Sofía  | María

La tabla clientes aparece dos veces, pero cada instancia representa un papel diferente.

cliente representa a la persona que fue referida y referente representa a quien la refirió.

8. La condición ON es la parte que conecta las tablas

En la mayoría de estos JOIN aparece una condición como esta:

ON clientes.id = pedidos.cliente_id

Esta condición le indica al motor de base de datos qué registros deben considerarse relacionados.

No es una comparación arbitraria. Está utilizando las columnas que representan la relación entre las dos tablas.

9. ¿Cuál JOIN debería usar?

Podemos tomar el mismo problema y cambiar únicamente el tipo de JOIN según lo que queremos conservar.

SELECT clientes.nombre, pedidos.producto
FROM clientes
INNER JOIN pedidos
  ON clientes.id = pedidos.cliente_id;

Solo clientes con pedidos.

SELECT clientes.nombre, pedidos.producto
FROM clientes
LEFT JOIN pedidos
  ON clientes.id = pedidos.cliente_id;

Todos los clientes, tengan o no pedidos.

SELECT clientes.nombre, pedidos.producto
FROM clientes
RIGHT JOIN pedidos
  ON clientes.id = pedidos.cliente_id;

Todos los pedidos, tengan o no un cliente correspondiente.

SELECT clientes.nombre, pedidos.producto
FROM clientes
FULL OUTER JOIN pedidos
  ON clientes.id = pedidos.cliente_id;

Todos los clientes y todos los pedidos, coincidan o no.

SELECT clientes.nombre, pedidos.producto
FROM clientes
CROSS JOIN pedidos;

Todas las combinaciones posibles entre clientes y pedidos.

10. La forma más sencilla de recordarlos

Puedes pensar en los JOIN como distintas respuestas a una misma pregunta: ¿qué registros quiero conservar cuando relaciono estas tablas?

INNER JOIN -> solo los que coinciden
LEFT JOIN  -> todo lo de la izquierda
RIGHT JOIN -> todo lo de la derecha
FULL JOIN  -> todo de ambos lados
CROSS JOIN  -> todas las combinaciones
SELF JOIN  -> una tabla relacionada consigo misma

Consideraciones

Los JOIN son una de las operaciones centrales de SQL porque las bases de datos suelen distribuir la información entre varias tablas relacionadas.

El tipo de JOIN determina qué registros sobreviven cuando existen coincidencias y cuando no las existen. Por eso aprender la sintaxis no es suficiente. Hay que entender qué lado de la relación se quiere conservar y qué condición conecta las tablas.

Con un solo caso, como clientes y pedidos, se pueden observar casi todas las diferencias importantes entre los principales tipos de JOIN.

© Arturo Juárez Monroy