SQL Joins explicados: Guía visual con ejemplos

Los joins SQL son la base de las bases de datos relacionales. Permiten combinar datos de dos o más tablas basándose en columnas relacionadas. Esta guía utiliza diagramas visuales y ejemplos prácticos para aclarar cada tipo de join.

Datos de ejemplo: Nuestras dos tablas

En esta guía, usaremos estas dos tablas simples:

-- employees table
+----+----------+---------------+
| id | name     | department_id |
+----+----------+---------------+
| 1  | Alice    | 1             |
| 2  | Bob      | 2             |
| 3  | Charlie  | 1             |
| 4  | Diana    | NULL          |
+----+----------+---------------+

-- departments table
+----+-------------+
| id | dept_name   |
+----+-------------+
| 1  | Engineering |
| 2  | Marketing   |
| 3  | Sales       |
+----+-------------+

INNER JOIN

Devuelve solo las filas que tienen valores coincidentes en ambas tablas.

    Table A         Table B
  +---------+     +---------+
  |         |     |         |
  |    +----+-----+----+    |
  |    |  INNER JOIN   |    |
  |    +----+-----+----+    |
  |         |     |         |
  +---------+     +---------+

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Resultado:

+----------+-------------+
| name     | dept_name   |
+----------+-------------+
| Alice    | Engineering |
| Bob      | Marketing   |
| Charlie  | Engineering |
+----------+-------------+
-- Diana excluded (department_id is NULL)
-- Sales excluded (no employee in Sales)

Usa cuando solo necesitas registros que existen en ambas tablas.

LEFT JOIN

Devuelve todas las filas de la tabla izquierda y las filas coincidentes de la tabla derecha.

    Table A         Table B
  +---------+     +---------+
  |XXXXXXXXX|     |         |
  |XXXX+----+-----+----+    |
  |XXXX| LEFT JOIN XXXX|    |
  |XXXX+----+-----+----+    |
  |XXXXXXXXX|     |         |
  +---------+     +---------+
  (all of A + matching B)

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Resultado:

+----------+-------------+
| name     | dept_name   |
+----------+-------------+
| Alice    | Engineering |
| Bob      | Marketing   |
| Charlie  | Engineering |
| Diana    | NULL        |
+----------+-------------+
-- Diana included with NULL dept_name

Usa cuando necesitas todos los registros de la tabla izquierda.

RIGHT JOIN

Devuelve todas las filas de la tabla derecha y las filas coincidentes de la tabla izquierda.

    Table A         Table B
  +---------+     +---------+
  |         |     |XXXXXXXXX|
  |    +----+-----+XXXX+XXXX|
  |    |XXXX RIGHT JOINXXXX|
  |    +----+-----+XXXX+XXXX|
  |         |     |XXXXXXXXX|
  +---------+     +---------+
  (matching A + all of B)

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Resultado:

+----------+-------------+
| name     | dept_name   |
+----------+-------------+
| Alice    | Engineering |
| Charlie  | Engineering |
| Bob      | Marketing   |
| NULL     | Sales       |
+----------+-------------+
-- Sales included with NULL name

Usa cuando necesitas todos los registros de la tabla derecha.

FULL OUTER JOIN

Devuelve todas las filas de ambas tablas. Los valores faltantes se rellenan con NULL.

    Table A         Table B
  +---------+     +---------+
  |XXXXXXXXX|     |XXXXXXXXX|
  |XXXX+----+-----+XXXX+XXXX|
  |XXXX|FULL OUTER JOINXXXX|
  |XXXX+----+-----+XXXX+XXXX|
  |XXXXXXXXX|     |XXXXXXXXX|
  +---------+     +---------+
  (everything from both)

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Resultado:

+----------+-------------+
| name     | dept_name   |
+----------+-------------+
| Alice    | Engineering |
| Bob      | Marketing   |
| Charlie  | Engineering |
| Diana    | NULL        |
| NULL     | Sales       |
+----------+-------------+

Usa cuando necesitas todos los registros de ambas tablas.

CROSS JOIN

Devuelve el producto cartesiano de ambas tablas. No necesita cláusula ON.

SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

-- Returns 4 × 3 = 12 rows

Resultado:

+----------+-------------+
| name     | dept_name   |
+----------+-------------+
| Alice    | Engineering |
| Alice    | Marketing   |
| Alice    | Sales       |
| Bob      | Engineering |
| Bob      | Marketing   |
| Bob      | Sales       |
| Charlie  | Engineering |
| Charlie  | Marketing   |
| Charlie  | Sales       |
| Diana    | Engineering |
| Diana    | Marketing   |
| Diana    | Sales       |
+----------+-------------+

Para generar combinaciones o datos de prueba.

Self JOIN (Auto-unión)

Una tabla unida consigo misma. Útil para datos jerárquicos.

-- employees_v2 with manager_id column
+----+---------+------------+
| id | name    | manager_id |
+----+---------+------------+
| 1  | Alice   | NULL       |
| 2  | Bob     | 1          |
| 3  | Charlie | 1          |
| 4  | Diana   | 2          |
+----+---------+------------+

SELECT
  e.name AS employee,
  m.name AS manager
FROM employees_v2 e
LEFT JOIN employees_v2 m ON e.manager_id = m.id;

Resultado:

+----------+---------+
| employee | manager |
+----------+---------+
| Alice    | NULL    |
| Bob      | Alice   |
| Charlie  | Alice   |
| Diana    | Bob     |
+----------+---------+

Para relaciones empleado-gerente o comparaciones de filas.

¿Qué join usar? Referencia rápida

Consejos de rendimiento

• Siempre indexa tus columnas de JOIN.
• Evita SELECT * en producción.
• Coloca la tabla más pequeña primero en FROM.
• Usa EXPLAIN ANALYZE para inspeccionar el plan de ejecución.
• Prefiere EXISTS sobre IN para subconsultas correlacionadas.
• Evita joins sobre expresiones o funciones.

-- Create index on JOIN column
CREATE INDEX idx_emp_dept ON employees(department_id);

-- Use EXPLAIN to check query plan
EXPLAIN ANALYZE
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Errores comunes

-- ❌ Wrong: WHERE turns LEFT JOIN into INNER JOIN
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.dept_name = 'Engineering';   -- filters out NULLs!

-- ✅ Correct: Put condition in ON clause
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.id
  AND d.dept_name = 'Engineering';   -- preserves NULLs

Preguntas frecuentes