¿Qué es un self join en SQL y cómo funciona? ¿Cuándo se debe utilizar? Vamos a responder a estas preguntas.

En SQL, podemos combinar datos de varias tablas utilizando un operador JOIN. JOIN tiene varias variantes; ya hemos hablado de CROSS JOIN, INNER JOIN y OUTER JOIN. La mayoría de las veces, estos operadores unen datos de dos o más tablas diferentes. Puede practicar todos los tipos de JOINs en nuestro curso interactivo Tipos de JOIN en SQL interactivo. En este artículo, sin embargo, explicaremos cómo unir registros de una misma tabla.

Una autounión SQL une datos de la misma tabla. En otras palabras, une una tabla consigo misma. Los registros tomados de la tabla se emparejan con otros registros de la misma tabla. ¿Por qué hacer esto? Puede necesitar comparar un valor con otro valor de la misma fila. No puede hacerlo a menos que una la tabla consigo misma y compare los valores como si estuvieran en dos registros separados.

No existe un operador específico para este tipo de consulta. En su lugar, la autounión SQL utiliza el operador INNER JOIN, uno de los operadores OUTER JOIN o un CROSS JOIN. La diferencia es que una sola tabla aparece como tabla izquierda y derecha en la unión.

Veamos un ejemplo de self join SQL basado en la tabla employee, que se muestra a continuación:

La tabla employee almacena los números de identificación de los empleados, sus nombres, apellidos, salarios y el número de identificación de su jefe. A excepción del jefe (cuyo ID = 1), todos tienen un supervisor. Sólo el jefe puede tener un valor NULL en la columna manager_id.

Intentemos una consulta que devuelva el superior inmediato de cada empleado:

SELECT e.id, e.first_name, e.last_name,  e.salary, 
  m.first_name AS fname_boss, m.last_name AS lname_boss
FROM employee e
JOIN employee m ON  e.manager_id = m.id ;

Observe que hemos utilizado la tabla employee como tabla izquierda y derecha en la unión. Para unir datos de la misma tabla, tuvimos que asignar dos alias al nombre de la tabla.

Debes utilizar alias cuando realices autouniones. Como estás uniendo columnas de la misma tabla, tendrán los mismos nombres. El alias cambia el nombre de las columnas para que el motor de la base de datos pueda ejecutar la consulta. En la consulta anterior, hemos utilizado la letra e como alias para la tabla de la izquierda. En este caso, estamos utilizando la tabla employee para sus registros de empleados. Utilizamos la letra m como alias para la tabla de la derecha, que utiliza la tabla employee para sus registros de gerente. Aunque estamos usando una sola tabla, SQL la trata como dos tablas diferentes.

Seleccionamos los datos de los empleados seleccionando las columnas id, first_name y last_name en la e "tabla. Los datos de los gestores se seleccionaron consultando la m "tabla". Fíjate en que los registros de los empleados llevaban el prefijo "e" y los de los directivos el prefijo "m". Ahora podemos unirlos porque hemos utilizado una condición de unión adecuada: la columna manager_id de la tabla e se comparó con la columna id de la tabla m. Los registros de ambas tablas se compararon con el operador JOIN (o INNER JOIN; recuerde que el "INNER" está implícito).

¿Se ha dado cuenta de que el jefe no aparece en los resultados? No hay ninguna coincidencia en la tabla derecha "manager", por lo que no se ha podido encontrar ningún dato sobre el superior del jefe.

La imagen siguiente muestra cómo se emparejan los registros durante la ejecución de esta consulta.

Analicemos los datos de la tabla de salida:

En nuestra tabla, la JOIN no ha devuelto ningún registro de John Watson. No tiene un identificador que apunte al id de su superior, por lo que es el jefe. Tendríamos que utilizar un LEFT JOIN en lugar de un INNER JOIN si quisiéramos devolver los datos de todos los empleados, incluido el jefe. En ese caso, también se mostrarían los registros de la tabla de la izquierda que no coincidan con los de la tabla de la derecha:

SELECT e.id, e.first_name, e.last_name,  e.salary, m.first_name 
   AS fname_boss  , m.last_name  AS lname_boss
FROM employee e
LEFT JOIN employee m ON  e.manager_id = m.id ;

Nuestro curso interactivo Tipos de JOIN en SQL tiene toda una sección dedicada a practicar los auto-uniones.

¿Cuándo debemos utilizar la autounión en SQL?

Las autouniones se utilizan comúnmente en las siguientes áreas:

• Relaciones jerárquicas
• Relaciones secuenciales
• Datos gráficos

Veremos cada una de ellas por separado.

Datos jerárquicos

El procesamiento de datos jerárquicos es una de las aplicaciones más frecuentes del self join de SQL. Esto ocurre cuando hay una columna adicional que apunta a un identificador en la misma tabla, como en nuestra employee tabla. En nuestro caso, la columna manager_id hace referencia (tiene el mismo valor) a la columna id.

El ejemplo anterior (la relación empleado-gerente) no es la única situación en la que se utilizan datos jerárquicos. A lista de materiales de un coche comparte una estructura similar. Cada coche consta de múltiples componentes, como el motor, el sistema de frenos y el sistema eléctrico. Cada uno de estos componentes está formado por partes más pequeñas. El sistema eléctrico de un coche puede dividirse en sus componentes, como la batería y el alternador (que pueden dividirse en aún más piezas del coche). Esto significa que las piezas del coche constituyen un grupo de datos jerárquicos. La tabla car tabla que se presenta a continuación muestra algunos datos de las piezas del coche.

Otro ejemplo de datos jerárquicos es la relación padre-hijo. Al almacenar esta relación, podemos utilizar una única tabla para albergar todo un árbol genealógico. A continuación, podemos utilizar una autounión SQL para recuperar fácilmente los datos sobre los antepasados de una persona determinada.

La siguiente tabla puede ayudarnos a identificar rápidamente el o los antepasados más antiguos de una familia. Estas personas no tienen datos de antepasados en sus registros, lo que significa que forman la raíz del árbol genealógico.

La tabla person tabla presentada a continuación lo ilustra:

La consulta siguiente recupera el nombre y el apellido de cada persona junto con el nombre y el apellido de su madre y su padre.

SELECT c.first_name, c.last_name, m.first_name AS fname_mother, m.last_name  AS lname_mother
FROM family c
LEFT JOIN person m ON  c.mather_id = m.id 
LEFT JOIN person f ON  c.father_id = f.id ;

Tenga en cuenta que hemos tenido que utilizar un self join tres veces (es decir, en tres "tablas" o alias) para obtener los datos del padre y de la madre. John Watson, Anne Brown y Jacob Miller no tienen datos que apunten a sus antepasados.

Los ejemplos de estructuras jerárquicas comentados anteriormente almacenan los datos utilizando un enfoque de registro superior-inferior. Esto nos permite presentar los datos como una estructura de árbol. Aprenderás más sobre el procesamiento de estructuras de árbol en SQL a medida que continúes aprendiendo sobre las bases de datos relacionales.

Datos secuenciales

Los datos secuenciales también pueden beneficiarse del uso de un self join en SQL. Por ejemplo, suponga que tiene registros que describen los pasos consecutivos necesarios para preparar un plato. Todos los pasos pueden colocarse en una sola tabla. Su orden se determina en función de las columnas que apuntan a los ID de los registros anterior y siguiente de la misma tabla.

Ilustremos esto utilizando la instruction tabla:

Como ves, en el orden actual estas instrucciones no tienen sentido. Pero cuando utilizamos un self join de SQL para mostrar la relación secuencial de cada paso de la receta, obtenemos:

1. Precalentar un horno a 220 grados C.
2. Pelar cuatro patatas.
3. Cortar las patatas en rodajas.
4. Mezclar las patatas en rodajas con aceite.
5. Cocer en el horno precalentado durante 20 minutos.
6. Sazonar las rodajas calientes con sal y pimienta.

¡También conseguimos unas sabrosas patatas fritas al horno!

Gráficos

Self join SQL también puede utilizarse para mostrar las relaciones necesarias para los gráficos. Un gráfico es una estructura formada por nodos conectados entre sí mediante aristas (relaciones). Un ejemplo de gráfico es la red de carreteras entre varias ciudades.

Fíjate en el siguiente dibujo.

Este gráfico representa cinco ciudades que están conectadas entre sí. Cada flecha muestra una carretera que va de una ciudad a otra. En este caso, las ciudades son los nodos y las carreteras entre ellas son las aristas. Estamos utilizando dos tablas para almacenar estos datos. La tabla city almacena el número de identificación y el nombre de cada ciudad. La tabla route contiene el número de identificación de la ruta, la ciudad de inicio (la columna from_city_id ) y la ciudad de destino (la columna to_city_id ).

Esta es la tabla "ciudad":

Y esta es la tabla "ruta":

Podemos utilizar una autounión SQL en la tabla city junto con una INNER JOIN de las dos tablas, para averiguar qué rutas existen entre las ciudades.

Mira la consulta.

SELECT c1.name AS from_city, c2.name AS to_city 
FROM city c1
JOIN route r ON c1.id = r.from_city_id
JOIN city c2  ON c2.id = r.to_city_id ;

Las columnas city y route se han unido utilizando la columna id de city y la columna from_city_id de route. En este punto, sólo podíamos recuperar el nombre de la ciudad inicial. Para recuperar el nombre de la ciudad de destino, utilizamos una autounión en la tabla city tabla. Esta vez, comparamos el id de la tabla de alias city con la columna to_city_id de la tabla route de la tabla.

Este es el resultado:

También puedes utilizar un self join de SQL en subconsultas recursivas que almacenan gráficos. Puede encontrar más información sobre este tema en nuestro nuevo curso, "Consultas recursivas y expresiones de tabla comunes".

Uso de SQL Self Join para encontrar valores duplicados

Las autouniones también pueden utilizarse para identificar valores duplicados en una tabla. Introduzcamos una tabla de ejemplo llamada color:

Cada registro de la tabla es diferente debido a la columna id, que siempre debe ser única. Pero esto no impide que dos filas almacenen el mismo nombre de color. Queremos identificar estos casos y encontrar los ID de los nombres de color duplicados. Vamos a probarlo:

SELECT c1.id AS id1, c1.name  AS color1, c2.id AS id2, c2.name AS color2
FROM color c1
JOIN color c2 ON c1.name = c2.name AND c1.id < c2.id   ; 

Pudimos encontrar los nombres de colores duplicados porque auto-unimos los registros basándonos en el nombre del color. La segunda condición se utiliza para omitir los registros idénticos de ambas tablas, así como los mismos pares de registros en orden inverso.

Observe el resultado de la consulta:

Ahora es fácil ver que hay valores duplicados para el azul y el amarillo.

Más información

Si desea aprender más sobre JOINs de lo que hemos cubierto en esta guía ilustrada de auto-uniones SQL, consulte nuestro curso Tipos de JOIN en SQL . Recuerde, la mejor manera de aprender Tipos de JOIN en SQL - ¡o cualquier otra cosa - es aprender haciendo!