SELF JOIN en SQLite: unir una tabla consigo misma

Un self join es solo un JOIN con alias

Un self join no tiene nada de especial. Es un JOIN normal y corriente, solo que ambos lados resultan ser la misma tabla. El truco está en que SQLite necesita distinguir entre las dos copias, así que le pones un alias a cada una.

Vas a recurrir a un self join en SQLite siempre que una fila de la tabla haga referencia a otra fila de esa misma tabla. El ejemplo clásico: una tabla employees donde cada fila tiene un manager_id que apunta a otro empleado:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT * FROM employees;

Ada no tiene jefe. Boris y Cleo le reportan a Ada. Diego y Esme le reportan a Boris. Toda la relación vive dentro de una sola tabla, y ahí es justo donde un self join en SQLite se gana el sueldo.

La estructura básica de un self join

Para emparejar a cada empleado con el nombre de su jefe, unimos la tabla employees consigo misma. Una copia hace el papel de "empleado" y la otra el de "jefe":

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS empleado,
    m.name AS jefe
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.id;

Léelo como dos tablas que casualmente comparten el mismo almacenamiento. e es la fila del empleado; m, la del jefe. La condición de unión e.manager_id = m.id las empareja: por cada empleado, busca la fila en m cuyo id coincida con el manager_id de ese empleado.

Fíjate en que Ada no aparece. Su manager_id es NULL, y el INNER JOIN descarta las filas que no encuentran pareja.

Conservar las filas sin coincidencia: LEFT JOIN

Si quieres que aparezcan todos en el resultado, incluso quienes no tienen jefe, cambia a LEFT JOIN:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS empleado,
    m.name AS jefe
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
ORDER BY e.id;

Ahora Ada aparece con NULL en la columna del jefe. La mecánica del self join es la misma; lo único que cambia es el tipo de join, que hace lo de siempre con LEFT JOIN: conservar todas las filas de la tabla de la izquierda y rellenar con vacíos cuando no hay coincidencia.

Esta suele ser la forma que quieres cuando muestras un listado de personas. "Sin jefe" también es información; descartar esa fila no tiene sentido.

Los alias de tabla no son opcionales

Si intentas hacer el join sin alias, SQLite no tiene forma de saber a qué te refieres:

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- Error: ambiguous column name: name

Cada columna aparece dos veces — una por cada copia de la tabla — y SQLite no sabe cuál usar. Los alias resuelven el problema dándole un nombre propio a cada instancia. Elige alias que describan el rol que cumple la fila, no el de la tabla:

• e y m para empleado/jefe.
• parent y child para jerarquías.
• a y b cuando comparas pares cualesquiera.

El alias es justamente lo que hace que un self join se lea con claridad.

Encontrar pares dentro de una misma tabla

El self join en SQLite no se limita a las jerarquías. Sirve siempre que quieras comparar filas de la misma tabla. Aquí tienes una lista de productos en la que buscamos todos los pares que tengan el mismo precio:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Taza',     9.99),
    ('Cuaderno', 9.99),
    ('Bolígrafo',     2.50),
    ('Lápiz',  2.50),
    ('Lámpara',   29.00);

SELECT a.name AS product_a, b.name AS product_b, a.price
FROM products AS a
INNER JOIN products AS b
    ON a.price = b.price
   AND a.id < b.id;

Hay dos detalles que conviene tener en el radar. Primero, a.price = b.price es la condición real de coincidencia. Segundo, a.id < b.id es lo que evita que la consulta devuelva cada par dos veces (una como ('Taza', 'Cuaderno') y otra como ('Cuaderno', 'Taza')), y de paso impide que cada fila se empareje consigo misma. Ese truco del < vale la pena memorizarlo: aparece siempre que necesitas listar pares.

Subiendo dos niveles en la jerarquía

Un self join resuelve un salto dentro de una jerarquía. ¿Quieres saber quién es el jefe del jefe de cada empleado? Pues haz el join tres veces:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name      AS employee,
    m.name      AS manager,
    gm.name     AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m  ON e.manager_id = m.id
LEFT JOIN employees AS gm ON m.manager_id = gm.id
ORDER BY e.id;

Cada nuevo alias representa un nivel más arriba en el árbol. La cosa funciona bien para dos o tres saltos, pero se rompe rápido: tendrías que conocer de antemano la profundidad de la jerarquía y añadir un JOIN por cada nivel. Justo ese es el muro que vinieron a derribar los CTE recursivos.

Cuándo no conviene usar un self join

El self join es la herramienta adecuada cuando necesitas columnas de ambos lados de la relación en el resultado. Si lo único que quieres es filtrar —por ejemplo, encontrar a todos los empleados cuyo jefe es Ada—, una subconsulta suele leerse mucho mejor:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT name
FROM employees
WHERE manager_id = (SELECT id FROM employees WHERE name = 'Ada');

Sin malabares con alias, y la intención queda clarísima. La regla práctica: ¿necesitas datos de ambas filas en el resultado? Self join. ¿Solo te hace falta un valor para comparar? Subconsulta.

Para jerarquías de profundidad arbitraria (organigramas, árboles de archivos, hilos de comentarios), ninguno de los dos enfoques escala bien. Ahí entran los CTE recursivos.

Lo que viene: subconsultas

El self join y las subconsultas resuelven problemas que se solapan, y saber cuál encaja mejor te ahorra muchos dolores de cabeza leyendo SQL más adelante. En la siguiente página entramos a fondo en las subconsultas — escalares, correlacionadas y con IN — y veremos dónde brilla cada una.