Aprende SQL · lección gratuita

Lección 20 · Auto-relaciones: SELF JOIN

Resumen

Un SELF JOIN es una tabla unida consigo misma. No es una sintaxis nueva: es un JOIN normal en el que ambos lados son la misma tabla, distinguidos por alias diferentes. Se usa cuando una fila se relaciona con otra fila de la misma tabla, como un empleado con su jefe (ambos están en empleados), o para comparar filas entre sí.

• Dos alias obligatorios: FROM empleados e JOIN empleados j — e es el empleado, j es su jefe. Sin alias distintos, SQLite no puede diferenciar las dos copias.
• Jerarquía empleado↔jefe: e.jefe_id = j.id conecta cada empleado con la fila de su superior.
• INNER vs LEFT y el CEO: el CEO (id=1) tiene jefe_id = 0, que no existe. Con INNER JOIN el CEO desaparece (no encuentra jefe). Con LEFT JOIN el CEO aparece con el jefe en NULL.
• Comparar filas: con a JOIN b ON a.x < b.x (misma tabla) puedes emparejar registros para comparar precios, fechas o salarios entre sí.
• Evita duplicados al comparar: usa a.id < b.id (en vez de <>) para no obtener cada par dos veces.

Sintaxis / Conceptos

La clave mental es tratar cada alias como si fuera una tabla independiente. e y j son la misma tabla física empleados, pero el motor las procesa como dos conjuntos separados que se emparejan por el ON.

El caso del CEO ilustra perfectamente por qué importa elegir INNER o LEFT. La condición e.jefe_id = j.id busca un empleado con id = 0; como no existe, el CEO no tiene pareja. Si quieres el organigrama completo (incluido quien manda), necesitas LEFT JOIN para que el CEO salga con jefe = NULL. Puedes mostrar un texto amigable con COALESCE(j.nombre, 'Sin jefe (CEO)').

SELECT e.nombre AS empleado, j.nombre AS jefe FROM empleados e JOIN empleados j ON e.jefe_id = j.id LIMIT 6

Ejemplos

-- Cada empleado con el nombre de su jefe.
-- LEFT JOIN para que el CEO (jefe_id=0) también aparezca, con jefe NULL.
SELECT e.nombre AS empleado, e.cargo,
       COALESCE(j.nombre, 'Sin jefe (CEO)') AS jefe
FROM empleados e
LEFT JOIN empleados j ON e.jefe_id = j.id
ORDER BY e.id;

-- INNER JOIN: excluye al CEO (no tiene jefe) → 21 filas en vez de 22
SELECT e.nombre AS empleado, j.nombre AS jefe, j.cargo AS cargo_jefe
FROM empleados e
INNER JOIN empleados j ON e.jefe_id = j.id
ORDER BY j.nombre, e.nombre;

-- Comparar filas: pares de empleados del MISMO departamento.
-- a.id < b.id evita repetir el par y compararse consigo mismo.
SELECT a.nombre AS empleado_a, b.nombre AS empleado_b, a.departamento_id AS depto
FROM empleados a
JOIN empleados b ON a.departamento_id = b.departamento_id AND a.id < b.id
ORDER BY a.departamento_id, a.nombre, b.nombre;

-- Detectar AUSENCIA de relación: empleados SIN subordinados (las "hojas" del organigrama).
-- LEFT JOIN de la tabla consigo misma y WHERE s.id IS NULL → nadie los tiene como jefe.
SELECT e.nombre AS empleado, e.cargo
FROM empleados e
LEFT JOIN empleados s ON s.jefe_id = e.id
WHERE s.id IS NULL
ORDER BY e.nombre;

💡 Si un SELF JOIN devuelve menos filas de las esperadas, sospecha de un INNER JOIN que está eliminando la raíz de la jerarquía (como el CEO). Cambia a LEFT JOIN para conservarla.

Cheatsheet

Forma	Qué hace
FROM t a JOIN t b ON ...	Une una tabla consigo misma (alias distintos)
e.jefe_id = j.id	Relaciona empleado con su jefe en la misma tabla
LEFT JOIN (self)	Conserva la raíz de la jerarquía (CEO con jefe NULL)
INNER JOIN (self)	Excluye filas sin pareja interna (el CEO)
ON a.id < b.id	Genera pares únicos al comparar filas entre sí
LEFT JOIN ... WHERE s.id IS NULL	Detecta ausencia de relación (hojas: empleados sin subordinados)

---

← FULL OUTER JOIN y CROSS JOINJOINs de múltiples tablas →

Ver todas las lecciones de Aprende SQL →