SQL · Guías · 30 de octubre de 2026 · 11 min de lectura

Self join en SQL: unir una tabla consigo misma, con ejemplos

Un self join es un JOIN normal en el que los dos lados son la misma tabla. Escribes la tabla dos veces con dos alias, por ejemplo flights f1 JOIN flights f2, y la condición del JOIN dice cómo se relaciona una fila de la izquierda con una de la derecha: la misma ciudad, el siguiente tramo de un viaje, la versión que vino después en un historial. No existe la palabra clave SELF JOIN; vale cualquier tipo de JOIN (INNER, LEFT, incluso CROSS). El self join responde a preguntas sobre relaciones entre filas de una misma tabla: pares, secuencias y conexiones.

Los ejemplos usan la base de datos de una aerolínea del curso de SQL de Nomto (104 aeropuertos, 33.121 vuelos, 366.733 billetes), con sintaxis de PostgreSQL sobre DuckDB, el motor de la sala de SQL de Nomto. Cada tabla de resultados es la salida real de la consulta. La sintaxis es la misma en MySQL, SQL Server u Oracle; donde cambia algo, lo indicamos.

Sintaxis del self join

SELECT a.col, b.col
FROM some_table a
JOIN some_table b
  ON <how row a relates to row b>;

Los alias son obligatorios. Sin ellos, airports.city = airports.city es ambiguo y no podrías decir de qué copia sale cada columna. En cuanto la consulta pasa de unas pocas líneas, usa alias que digan el papel de cada copia (origin y dest, prev y next, out y back) en lugar de a y b.

Pares únicos: a < b en lugar de a <> b

El self join más sencillo empareja filas que comparten algo. La guía de JOIN lo usa para encontrar ciudades con varios aeropuertos. Aquí va una consulta de pares sin clave común: los aeropuertos de ciudades distintas que están más cerca entre sí, por distancia ortodrómica calculada a partir de sus coordenadas.

SELECT a1.airport_code, a1.city,
       a2.airport_code AS other, a2.city AS other_city,
       ROUND(2 * 6371 * ASIN(SQRT(
         POWER(SIN(RADIANS(a2.latitude - a1.latitude) / 2), 2) +
         COS(RADIANS(a1.latitude)) * COS(RADIANS(a2.latitude)) *
         POWER(SIN(RADIANS(a2.longitude - a1.longitude) / 2), 2)
       ))) AS km
FROM airports a1
JOIN airports a2 ON a1.airport_code < a2.airport_code
WHERE a1.city <> a2.city
ORDER BY km
LIMIT 6;

La condición del ON decide cuántas filas obtienes. Con 104 aeropuertos:

El truco del < funciona con cualquier columna que tenga un orden fijo: un ID, un código, un nombre. Elimina de una vez las parejas de una fila consigo misma y los pares espejo, y reduce el trabajo a la mitad. Usa <> solo cuando el sentido importa, por ejemplo «A está más cerca de B que de cualquier otro», donde A y B juegan papeles distintos. La consulta compara cada aeropuerto con todos los demás: con cien filas no pasa nada, pero el coste crece con el cuadrado del tamaño de la tabla, así que en tablas grandes añade una condición que reduzca los candidatos (la misma región, un rango de latitud).

Vuelos con escala: el final de una fila con el inicio de otra

Los self joins más útiles encadenan eventos: la llegada de un vuelo unida a una salida desde el mismo aeropuerto poco después. Conexiones desde Kazán vía Moscú-Domodédovo el 20 de agosto, con una escala de entre una y cuatro horas:

SELECT f1.flight_no AS leg1, f1.arrival_airport AS via,
       f1.scheduled_arrival::time AS lands,
       f2.flight_no AS leg2, f2.arrival_airport AS final_dest,
       f2.scheduled_departure::time AS departs,
       f2.scheduled_departure - f1.scheduled_arrival AS layover
FROM flights f1
JOIN flights f2
  ON  f2.departure_airport = f1.arrival_airport
  AND f2.scheduled_departure BETWEEN f1.scheduled_arrival + INTERVAL '1 hour'
                                 AND f1.scheduled_arrival + INTERVAL '4 hours'
  AND f2.arrival_airport <> f1.departure_airport
WHERE f1.departure_airport = 'KZN'
  AND f1.arrival_airport = 'DME'
  AND f1.scheduled_departure::date = '2026-08-20'
ORDER BY f2.scheduled_departure;

Un vuelo de tarde desde Kazán conecta con Bélgorod, Yuzhno-Sajalinsk y Jabárovsk. Tres detalles hacen que la consulta sea correcta:

• El JOIN no es por una clave. f2.departure_airport = f1.arrival_airport es una igualdad, pero la ventana de tiempo es una condición de rango. En los self joins es habitual mezclar las dos.
• La ventana de tiempo es relativa al primer tramo. BETWEEN arrival + 1 hour AND arrival + 4 hours descarta conexiones imposibles (salir antes de aterrizar) y absurdas (una salida al día siguiente).
• f2.arrival_airport <> f1.departure_airport elimina las «conexiones» que te devuelven directamente a casa.

El filtro de fecha del WHERE solo se aplica al primer tramo; el segundo puede salir al día siguiente si el primero aterriza tarde. Quita el filtro f1.arrival_airport = 'DME' y la misma consulta lista las 17 conexiones desde Kazán ese día, vía San Petersburgo, Rostov, Kírov y Domodédovo. Para encadenar tres tramos o más, repite el JOIN o pasa a una CTE recursiva.

En SQL Server, el ::time y el ::date se escriben CAST(... AS time) y CAST(... AS date), y los intervalos con DATEADD(hour, 1, f1.scheduled_arrival); en MySQL, f1.scheduled_arrival + INTERVAL 1 HOUR.

Viajes de ida y vuelta: una fila y su espejo

Un billete puede tener varios segmentos de vuelo. Un viaje de ida y vuelta es un billete en el que un segmento va de A a B y otro posterior de B a A: un self join de los segmentos del billete con los aeropuertos cruzados en la condición. Para simplificar, nos quedamos con los billetes de exactamente dos segmentos (183.338):

WITH seg AS (
  SELECT tf.ticket_no, f.departure_airport, f.arrival_airport, f.scheduled_departure
  FROM ticket_flights tf
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE tf.ticket_no IN (
    SELECT ticket_no FROM ticket_flights GROUP BY ticket_no HAVING COUNT(*) = 2
  )
)
SELECT outbound.departure_airport AS home, outbound.arrival_airport AS destination,
       COUNT(*) AS round_trips,
       ROUND(AVG(back.scheduled_departure::date - outbound.scheduled_departure::date), 1) AS avg_days_away
FROM seg outbound
JOIN seg back
  ON  back.ticket_no = outbound.ticket_no
  AND back.departure_airport = outbound.arrival_airport
  AND back.arrival_airport = outbound.departure_airport
  AND back.scheduled_departure > outbound.scheduled_departure
GROUP BY home, destination
ORDER BY round_trips DESC
LIMIT 5;

De todos los billetes de dos segmentos, 177.847 son de ida y vuelta, con una estancia media de 8,4 días. La condición back.scheduled_departure > outbound.scheduled_departure cumple el mismo papel que a < b más arriba: sin ella, cada viaje también coincidiría «al revés», con la vuelta como ida, y el recuento se duplicaría.

¿Por qué solo billetes de dos segmentos? Los billetes con escala, por ejemplo Moscú–Bélgorod–Anapa y vuelta, tienen cuatro segmentos, y la condición espejo empareja cada tramo con su propia vuelta: dos pares por billete. Unir una tabla consigo misma multiplica filas en cuanto una fila tiene varias coincidencias, el mismo efecto por el que un JOIN duplica filas en cualquier otro caso. Antes de agregar el resultado de un self join, comprueba qué representa una fila de la salida.

Historiales: unir cada versión con la siguiente

Las tablas que guardan versiones con valid_from y valid_to se pueden unir consigo mismas para poner cada versión al lado de la que la sustituyó. Cambios de nivel en el programa de fidelización, donde el fin de la fila antigua coincide con el inicio de la nueva:

SELECT prev.tier AS from_tier, nxt.tier AS to_tier, COUNT(*) AS moves
FROM loyalty_tier_history prev
JOIN loyalty_tier_history nxt
  ON  nxt.member_id = prev.member_id
  AND nxt.valid_from = prev.valid_to
GROUP BY from_tier, to_tier
ORDER BY moves DESC;

Es el clásico patrón «empleado y jefe» aplicado al tiempo: una fila apunta a otra de la misma tabla, aquí mediante fechas en lugar de una columna manager_id. Con un organigrama escribirías JOIN employees mgr ON mgr.employee_id = emp.manager_id, y un LEFT JOIN conservaría al director general, que no tiene jefe. Es la pregunta de entrevista más típica sobre self join: «lista cada empleado con el nombre de su jefe».

Self join frente a funciones de ventana

La tabla de niveles tiene 58.194 filas para 53.000 socios, así que debería haber 5.194 transiciones. El self join encontró 4.989. Hagamos la misma pregunta con LAG, que simplemente toma la fila anterior por fecha:

SELECT prev_tier AS from_tier, tier AS to_tier, COUNT(*) AS moves
FROM (
  SELECT tier,
         LAG(tier) OVER (PARTITION BY member_id ORDER BY valid_from) AS prev_tier
  FROM loyalty_tier_history
) t
WHERE prev_tier IS NOT NULL
GROUP BY from_tier, to_tier
ORDER BY moves DESC;

5.194 transiciones. Las 205 que se le escaparon al self join son filas cuyo valid_to no coincide con el valid_from siguiente: 110 versiones antiguas nunca se cerraron y 95 se cerraron unos días tarde, solapándose con la siguiente. Un JOIN por igualdad de fechas descarta esos enlaces sin avisar; a LAG el valid_to le da igual. La guía de SCD tipo 2 (en inglés) explica cómo encontrar y reparar esas filas.

Ninguna de las dos respuestas es «la» correcta. El self join se fía de los enlaces declarados; la función de ventana, del orden. La diferencia entre ambas ya es en sí un control de calidad de datos. Como regla práctica:

Las funciones de ventana leen la tabla una vez y la ordenan; un self join la lee dos veces y puede producir muchas coincidencias por fila. Cuando las dos sirven, la función de ventana suele ser más rápida y no multiplica filas. LAG y LEAD se explican a fondo en LAG y LEAD en SQL.

LEFT self join: conservar las filas sin pareja

Un self join INNER descarta las filas que no encuentran coincidencia. Para conservarlas, usa LEFT JOIN y comprueba NULL en el lado derecho. Las filas sin predecesor, es decir, la primera fila del historial de cada socio:

SELECT COUNT(*) AS rows_without_predecessor
FROM loyalty_tier_history cur
LEFT JOIN loyalty_tier_history prev
  ON  prev.member_id = cur.member_id
  AND prev.valid_to = cur.valid_from
WHERE prev.member_id IS NULL;

53.000 son las primeras filas de los socios. Las otras 205 son los enlaces rotos de la sección anterior, encontrados esta vez sin función de ventana. Este «anti self join» sirve para encontrar huérfanos: categorías cuyo padre no existe, vuelos sin regreso, versiones sin predecesora.

Errores frecuentes

• Olvidar la desigualdad. a.city = b.city a secas empareja cada aeropuerto consigo mismo; el resultado tiene más filas que la tabla y parece que todos los aeropuertos comparten ciudad.
• Poner <> donde querías <. Cada par aparece dos veces y los recuentos se duplican.
• Agregar después de multiplicar filas. Si una fila puede coincidir con varias, SUM y COUNT sobre el self join la cuentan varias veces.
• Unir por columnas que pueden ser NULL. NULL = NULL no es verdadero, así que las filas con clave NULL nunca encuentran pareja. Por eso no se unieron las 110 filas abiertas de arriba (más en NULL en SQL).
• No acotar en una tabla grande. Un self join con una condición débil se parece mucho a un producto cartesiano: un millón de filas contra un millón de filas.

Preguntas frecuentes

¿Qué es un self join en SQL?

Un JOIN de una tabla consigo misma. Haces referencia a la tabla dos veces con alias distintos y unes las copias con una condición, por ejemplo FROM employees e JOIN employees m ON m.employee_id = e.manager_id. Sirve para comparar filas de la misma tabla entre sí.

¿SELF JOIN es una palabra clave?

No. Un self join se escribe con la sintaxis habitual de JOIN, LEFT JOIN o CROSS JOIN; lo que lo convierte en self join es que los dos lados son la misma tabla. Funciona igual en MySQL, SQL Server, PostgreSQL y Oracle.

¿Cómo evito los pares duplicados en un self join?

Une por a.id < b.id en lugar de a.id <> b.id. Así eliminas las filas emparejadas consigo mismas y conservas solo uno de cada par espejo (A–B, pero no B–A).

¿Cuándo usar un self join en lugar de LAG o LEAD?

Usa LAG/LEAD para la fila anterior o siguiente en un orden fijo. Usa un self join cuando la relación se define por una condición y no por la posición: pares dentro de una distancia, conexiones dentro de una ventana de tiempo, una fila que apunta a otra por ID.

¿Un self join puede ser un LEFT JOIN?

Sí. Un self join LEFT conserva las filas sin pareja, con NULL en el lado derecho. Si añades WHERE lado_derecho.clave IS NULL, se convierte en un anti join que encuentra filas sin coincidencia, como registros cuyo padre no existe.

También en English, Português (Brasil), Tiếng Việt, 简体中文