Self join: unir una tabla consigo misma
IntermedioUn self join une una tabla consigo misma usando dos alias. Sirve para jerarquías (una categoría y su categoría padre, con parent_id) y para comparar filas de la misma tabla entre sí: restaurantes que compiten, publicaciones que parecen duplicadas.
Sintaxis
-- Jerarquía: cada fila con su padre
SELECT hija.name, padre.name AS padre
FROM categories AS hija
LEFT JOIN categories AS padre ON padre.id = hija.parent_id;
-- Pares: cada combinación una sola vez
SELECT a.id, b.id
FROM tabla AS a
INNER JOIN tabla AS b ON b.grupo = a.grupo AND a.id < b.id;Ejemplo
SELECT
a.name AS restaurante_a,
b.name AS restaurante_b,
a.commission_pct AS comision_a,
b.commission_pct AS comision_b
FROM restaurants AS a
INNER JOIN restaurants AS b
ON b.city_id = a.city_id
AND b.cuisine = a.cuisine
AND a.id < b.id
WHERE a.city_id = 8
AND a.cuisine = 'sushi'
ORDER BY a.id, b.id;Resultado
| restaurante_a | restaurante_b | comision_a | comision_b |
|---|---|---|---|
| El Rincón González 50 | El Rincón Díaz 67 | 22.00 | 22.00 |
| El Rincón González 50 | Casa Gómez 85 | 22.00 | 15.00 |
| El Rincón González 50 | La Esquina Vega 136 | 22.00 | 22.00 |
| El Rincón Díaz 67 | Casa Gómez 85 | 22.00 | 15.00 |
| El Rincón Díaz 67 | La Esquina Vega 136 | 22.00 | 22.00 |
| Casa Gómez 85 | La Esquina Vega 136 | 15.00 | 22.00 |
Cómo leerlo
Montevideo tiene cuatro restaurantes de sushi, y cada par de competidores aparece una sola vez: 4 × 3 / 2 = 6 pares. La condición a.id < b.id descarta el par de un restaurante consigo mismo y el mismo par en orden inverso. El resultado muestra que Casa Gómez 85 paga 15 % de comisión frente al 22 % de sus tres competidores.
Error común
Así no
SELECT a.name, b.name
FROM restaurants AS a
INNER JOIN restaurants AS b
ON b.city_id = a.city_id
AND b.cuisine = a.cuisine
AND a.id <> b.id
WHERE a.city_id = 8
AND a.cuisine = 'sushi';Usar a.id <> b.id para armar pares. Excluye a cada restaurante de su propio par, pero conserva (A, B) y (B, A): aquí devuelve 12 filas en vez de 6 y cualquier conteo de pares queda duplicado. Usa <> solo si necesitas las dos direcciones. Para obtener cada par una sola vez, usa a.id < b.id.
En otros motores: Oracle
Oracle
Un self join recorre un solo nivel de la jerarquía. Para recorrerla completa, sin saber cuántos niveles tiene, se usa una CTE recursiva; Oracle además tiene su sintaxis propia
CONNECT BY PRIOR.
Estos fragmentos no se ejecutan en el curso: se muestran como referencia de cada motor.