¿Qué es Self Join en SQL? Una explicación con siete ejemplos

Self join es una alternativa muy popular de JOIN (unión) en SQL. Mientras que la mayoría de los JOIN enlazan dos o más tablas entre sí para presentar sus datos de forma conjunta, una self join (autounión) enlaza una tabla consigo misma. Esto se suele hacer uniendo una tabla consigo misma una sola vez dentro de una consulta SQL, pero es posible hacerlo varias veces dentro de la misma consulta.

Normalmente, cada tabla de una base de datos almacena un tipo específico de información. Por lo tanto, a menudo hay cientos de tablas relacionadas entre sí en una base de datos. Esto implica la necesidad de realizar uniones. Se pueden unir diferentes tablas por sus columnas comunes utilizando la palabra clave JOIN. También es posible unir una tabla a sí misma, lo que se conoce como autounión (self-join). En este artículo, veremos qué es una autounión, cómo funciona y cuándo lo necesitas en tus consultas SQL.

Para practicar uniones JOIN en SQL, incluyendo las autouniones, recomiendo nuestro curso interactivo Tipos de JOIN en SQL. Contiene más de 90 ejercicios sobre diferentes tipos de JOIN, incluyendo secciones dedicadas sólo a las autouniones.

¿Qué es una autounión o Self Join en SQL?

Una autounión, como su nombre lo indica, une una tabla consigo misma. Para utilizar una autounión, la tabla debe contener una columna (llámese X) que actúe como clave primaria, y otra columna (llámese Y) que almacene valores que puedan coincidir con los valores de la columna X. Los valores de las columnas X e Y no tienen por qué ser los mismos para una fila determinada, y el valor de la columna Y puede ser incluso null.

Veamos un ejemplo. Consideremos la tabla empleados:

Cada empleado tiene su propia Id, que es nuestra "columna X". Para un empleado determinado (es decir, una fila), la columna id_gerente contiene el Id de su gerente; ésta es nuestra "columna Y". Si rastreamos los pares de empleados-gerentes en esta tabla utilizando estas columnas:

• El gerente del empleado John Smith es el empleado con Id 3, es decir, Tom Lanon.
• El gerente del empleado Jane Anderson es el empleado con Id 3, es decir, Tom Lanon.
• El gerente del empleado Tom Lanon es el empleado con Id 4, es decir, Anne Connor.
• La empleada Anne Connor no tiene gerente; su id_del_gerente es nulo.
• El gerente del empleado Jeremy York es el empleado con Id 1, es decir, John Smith.

Este tipo de estructura de tabla es muy común en las jerarquías. Ahora bien, para mostrar el nombre del gerente de cada empleado en la misma fila, podemos ejecutar la siguiente consulta:

SELECT
	empleado.Id,
    	empleado.nombre_completo,
    	empleado.id_gerente,
    	gerente.nombre_completo as nombre_gerente
FROM empleados empleado
JOIN empleados gerente
ON empleado.id_gerente = gerente.Id

Que devuelve el siguiente resultado:

La consulta selecciona las columnas Id, nombre_completo y id_gerente de la tabla con alias empleado. También selecciona la columna nombre_completo de la tabla con alias gerente y designa esta columna como nombre_gerente. Como resultado, cada empleado que tiene un gerente se muestra junto con su ID y nombre.

En esta consulta, la tabla empleados está unida a sí misma y cumple dos roles diferentes:

• Rol 1: Almacena los datos del empleado (alias empleado).
• Rol 2: Almacena los datos del gerente (alias gerente).

Al hacerlo, estamos considerando las dos copias de la tabla empleados como si fueran dos tablas distintas, una para los empleados y otra para los gerentes.

Puedes encontrar más información sobre el concepto de self join o autounión en nuestro artículo Guía ilustrada de Self Join de SQL.

Alias de tablas en Self Join

Cuando se hace referencia a la misma tabla más de una vez en una consulta SQL, necesitamos una forma de distinguir cada referencia de las demás. Por esta razón, es importante utilizar alias para identificar de forma única cada referencia de la misma tabla en una consulta SQL. Como buena práctica, los alias deben indicar el papel de la tabla para cada referencia específica en una consulta.

Los alias están en rojo en la siguiente consulta. Puedes ver su declaración en las cláusulas FROM y JOIN.

SELECT
	empleado.Id,
    	empleado.nombre_completo,
    	empleado.id_gerente,
    	gerente.nombre_completo as nombre_gerente
FROM empleados empleado
JOIN empleados gerente
ON empleado.id_gerente = gerente.Id

La palabra clave JOIN conecta dos tablas y suele ir seguida de una cláusula ON o USING que especifica las columnas comunes utilizadas para vincular las dos tablas. En este caso, vemos que las dos referencias a la tabla empleados se vinculan condicionando a que el id_gerente del empleado coincida con el ID del empleado del gerente.

Ejemplos

Veamos algunos escenarios comunes que utilizan Self Join.

Escenario 1: Procesamiento de una jerarquía en SQL

Self Join suele usarse en el procesamiento de una jerarquía. Como vimos anteriormente, una jerarquía asigna una fila en una tabla a otra fila dentro de la misma tabla. Se puede pensar que hay filas madres e hijas.

Volvamos al ejemplo con los empleados y sus gerentes. Aquí está la tabla empleados nuevamente:

Y el código para listar cada empleado que tiene un gerente, junto al nombre de su gerente:

SELECT
	empleado.Id,
    	empleado.nombre_completo,
    	empleado.id_gerente,
    	gerente.nombre_completo as nombre_gerente
FROM empleados empleado
JOIN empleados gerente
ON empleado.id_gerente = gerente.Id

Este es el resultado al ejecutar el código:

Esta consulta utiliza la unión estándar, también conocida como INNER JOIN. Para leer más sobre INNER JOIN, consulta nuestro artículo Guía ilustrada de INNER JOIN de SQL.

Si queremos listar todos los empleados, tengan o no tengan gerentes, podemos usar un LEFT OUTER JOIN en su lugar. La consulta de abajo hace lo siguiente:

SELECT
	empleado.Id,
    	empleado.nombre_completo,
    	empleado.id_gerente,
    	gerente.nombre_completo as nombre_gerente
FROM empleados empleado
LEFT OUTER JOIN empleados gerente
ON empleado.id_gerente = gerente.Id

Al ejecutar esta consulta, se obtiene el siguiente resultado:

La diferencia entre JOIN y LEFT OUTER JOIN queda clara cuando comparamos esto con el resultado de la consulta SQL anterior. En el resultado de la consulta (inner o interna) de JOIN, sólo se incluyen los empleados con gerentes. En cambio, la consulta LEFT OUTER JOIN devuelve todos los empleados, con o sin gerentes. Para saber más sobre OUTER JOIN, lee nuestro artículo Guía ilustrada de OUTER JOIN de SQL.

Otro ejemplo de jerarquía es la relación entre padres e hijos. Considera la tabla humano que se muestra aquí:

En la siguiente consulta, los hijos se asignan a sus respectivos padres uniendo la tabla humano a sí misma:

SELECT
	hijo.Id as id_hijo,
    	hijo.nombre as nombre_hijo,
    	hijo.edad as edad_hijo,
    	hijo.id_padre,
    	padre.nombre as nombre_padre,
    	padre.edad as edad_padre
FROM humano hijo
INNER JOIN humano padre
ON hijo.id_padre = padre.Id

Este es el resultado de esta consulta:

El resultado de la consulta incluye sólo los hijos que tienen padres. Al igual que en el ejemplo de la jerarquía empleado-gerente, podríamos utilizar un LEFT OUTER JOIN para incluir todas las filas de la tabla alias hijo.

He aquí otro ejemplo de jerarquía. Considera la tabla categoria que se muestra a continuación:

Asignemos una categoría padre a cada categoría, siempre que sea posible. Aquí hay una consulta para hacer eso:

SELECT
	categoria.Id,
	categoria.cantidad,
    	categoria.categoria,
    	categoria.id_categoria_padre,
    	padrecategoria.categoria as categoria_padre
FROM categoria categoria
JOIN categoria padrecategoria
ON categoria.id_categoria_padre = padrecategoria.Id

Y aquí está el resultado:

Las cuatro primeras columnas del resultado anterior proceden de la referencia a la tabla de alias categoria. La última columna procede de la tabla alias padrecategoria y contiene el nombre de la categoría padre que coincide con la respectiva Id.

La tabla categoria tiene dos funciones diferentes, como indican las dos referencias separadas. La columna id_categoria_padre de la tabla alias categoria se empareja con Id de la tabla alias padrecategoria. La cláusula ON especifica que id_categoria_padres de categoria debe ser igual a Id de padrecategoria para conectar las filas correspondientes.

Escenario 2: Listado de pares dentro de una tabla

Se puede utilizar Self Join para generar pares de filas basados en la condición de la cláusula ON. Empecemos con un ejemplo sencillo que genera todos los pares posibles entre colegas. Considera la siguiente tabla, colegas:

Supongamos que necesitamos generar todas los pares posibles entre los colegas para que todos tengan la oportunidad de hablar con todos los demás en la presentación de la empresa. Aquí está el código SQL:

SELECT
	companero1.nombre_completo as companero_equipo1_nombre_completo,
	companero1.edad as companero_equipo1edad,
    	companero2.nombre_completo as companero_equipo2nombre_completo,
	companero2.edad as companero_equipo2edad
FROM colegas companero1
CROSS JOIN colegas companero2
ON companero1.nombre_completo <> companero2.nombre_completo

Y aquí está el resultado:

El resultado empareja a cada persona con cada persona de la tabla. Como no queremos que nadie se empareje consigo mismo, tenemos la condición de la cláusula ON companero1.nombre_completo <> companero2.nombre_completo. Esto significa que cada persona será emparejada con otros tres compañeros, porque hay cuatro compañeros en este evento.

Ahora, veamos un ejemplo un poco más complicado. Considera la tabla humano que se muestra a continuación. Queremos emparejar todos los antepasados de cada persona siempre que los datos lo permitan, donde una persona es un antepasado si tiene un valor Id mayor.

A continuación, se muestra la tabla humano utilizada en este ejemplo.

Busquemos todos los pares descendiente-antepasado en la tabla anterior. Aquí está el código SQL:

SELECT
	descendiente.Id,
    	descendiente.nombre,
    	descendiente.edad,
    	descendiente.id_padre,
    	antepasado.Id as id_antepasado,
    	antepasado.nombre as nombre_antepasado,
    	antepasado.edad as edad_antepasado
FROM humano descendiente
LEFT JOIN humano antepasado
ON descendiente.id_padre <= antepasado.Id

Y el resultado:

Especificando la condición de la cláusula ON descendiente.id_padre <= antepasado.Id, encontramos todos los antepasado de cada persona en la tabla donde existen; de lo contrario, la consulta devuelve null para la información de los antepasados.

Las cuatro primeras columnas se toman de la tabla alias descendiente, que contiene la información de la persona para la que se buscan los antepasados. Las tres últimas columnas se toman de la tabla alias antepasado y contienen detalles sobre cada antepasado.

Escenario 3: Autounión en combinación con otra tabla

En SQL, es posible tener un Self Join en combinación con una o más tablas diferentes. Aunque no se trata de un Self Join limpia, esto es muy común en la práctica.

Un ejemplo de la vida real es la información de los vuelos en los aeropuertos, con una enorme cantidad de datos cada hora. Supongamos que queremos buscar un número de identificación de vuelo junto con los detalles de sus aeropuertos de salida y destino. Consideremos las siguientes tablas:

Tabla aeropuerto:

Tabla vuelo:

Aquí, observa que la columna id_aeropuerto de la tabla aeropuerto es la clave externa de las columnas id_aeropuerto_inicio e id_aeropuerto_fin de la tabla vuelo. Vamos a unir la tabla aeropuerto a la tabla vuelo dos veces por separado, como se indica a continuación:

• En el primer JOIN, aeropuerto toma el rol de la tabla con los aeropuertos de partida (inicio).
• En el segundo JOIN, aeropuerto toma el rol de la tabla con los aeropuertos de arribo (fin).

La consulta tiene el siguiente aspecto:

SELECT
	vuelo.id_vuelo,
    	vuelo.id_avion,
    	vuelo.id_aeropuerto_inicio,
    	aeropuertoinicio.pais as inicio_aeropuerto_pais,
    	aeropuertoinicio.ciudad as inicio_aeropuerto_ciudad,
    	vuelo.id_aeropuerto_fin,
    	aeropuertofin.pais as pais_aeropuerto_fin,
    	aeropuertofin.ciudad as ciudad_aeropuerto_fin
FROM vuelo vuelo
JOIN aeropuerto aeropuertoinicio
ON vuelo.id_aeropuerto_inicio = aeropuertoinicio.id_aeropuerto
JOIN aeropuerto aeropuertofin
ON vuelo.id_aeropuerto_fin = aeropuertofin.id_aeropuerto

Y el resultado de la consulta es el siguiente:

Analicemos el resultado. Las tres primeras columnas provienen de un sencillo SELECT de la table vuelo. Las dos columnas siguientes proceden de aeropuerto en el rol de tabla de aeropuerto de partida; las filas se emparejan en función de id_aeropuerto y id_aeropuerto_inicio de aeropuerto y vuelo respectivamente. A esto le sigue una columna de la tabla vuelo. Las dos últimas columnas proceden de aeropuerto, esta vez en el rol de la tabla del aeropuerto de destino; las filas se emparejan en base a id_aeropuerto y id_aeropuerto_fin de las tablas aeropuerto y vuelo respectivamente.

¿Te confunden un poco todos los JOIN? Hay muchos más artículos en los que puedes buscar ayuda. Te recomiendo especialmente el artículo sobre Cómo aprender Tipos de JOIN en SQL. Y si necesitas empezar a practicar, visita nuestro artículo sobre Cómo practicar Tipos de JOIN en SQL.

La autounión: un tipo especial de unión

Como hemos visto, la autounión es un tipo importante de unión. Hemos visto ejemplos de varias aplicaciones del Self Join, incluyendo el procesamiento de una jerarquía en una tabla y el emparejamiento de las filas dentro de una tabla. Podemos unir la misma tabla varias veces, pero es importante dar a cada referencia un alias que indique su función. Estos alias de tabla se utilizan para obtener columnas de esta tabla única en función del rol al que se hace referencia.

Las uniones son una parte vital de SQL, a la vez que una característica muy útil y frecuentemente utilizada para combinar diferentes tablas. ¡Están en todas partes! No olvides consultar nuestro curso sobre Tipos de JOIN en SQL para dominar esta poderosa herramienta.