Cómo unir la misma tabla dos veces

JOIN es una de las sentencias más comunes en SQL. Como sabrás, se utiliza para unir y combinar datos de dos o más tablas en un conjunto de datos común. En este artículo, voy a discutir tipos especiales de joins... en los que se combina la misma tabla dos veces-incluyendo la unión de una tabla a sí misma, también conocida como self join. ¿Cuándo y por qué es necesario hacer esto? ¿Cómo se escribe en SQL? Averigüémoslo.

Uniones: Un Rápido Repaso

Probablemente estés familiarizado con las uniones en SQL. Tienes dos tablas, A y B, y las combinas utilizando una columna común a ambas. Este es un ejemplo:

Tenemos dos tablas: customer y citycon una columna común llamada city_id.

Ahora, si quiere unirlas para obtener los nombres de las ciudades respectivas de los clientes, puede hacerlo con una unión como esta:

select customer.customer_id,
     customer.firstname,
     customer.lastname,
     customer.birthdate,
     customer.spouse_id,
     customer.city_id,
     city.name as city_name
from customer 
join city 
on customer.city_id = city.city_id;

En esta sentencia JOIN, hacemos coincidir los registros de customer y city por una clave (city_id). Recuperamos las 6 columnas de la tabla customer y una columna, name, de la tabla city tabla. Hay varios tipos de uniones en SQL; este ejemplo hace una INNER JOIN.

No voy a profundizar en la sintaxis de JOIN aquí. Para aprender más, consulta nuestro curso interactivo sobre uniones SQL que puedes encontrar en LearnSQL.es.

El resultado de esta unión será una tabla con los 6 campos de la tabla customer más un campo adicional de la tabla city de la tabla:

Unir la misma tabla varias veces

Ahora que hemos hecho un rápido repaso, veamos las uniones más complejas.

A veces es necesario unir la misma tabla varias veces. Generalmente, esto implica agregar una o más columnas a un conjunto de resultados de la misma tabla pero a diferentes registros o por diferentes columnas. Examinaremos dos escenarios de este tipo: unir una tabla a sí misma y unir tablas con múltiples relaciones.

Autounión: Unir una tabla a sí misma

La autounión es un caso especial de la unión. En lugar de unir dos tablas diferentes, se une una tabla a sí misma. ¿Por qué querríamos hacer esto?

En nuestro ejemplo anterior, queríamos añadir una columna de la tabla city el nombre de la ciudad, a la tabla customer tabla. Por lo tanto, hemos unido dos tablas diferentes entre sí. Hacer un self join significaría, por ejemplo, unir la customer a sí misma.

Aquí está la customer tabla como recordatorio:

La columna spouse_id almacena el customer_id del cónyuge del cliente. Por ejemplo, los clientes 1 y 2 (Juan y María) son cónyuges entre sí, los clientes 3 y 5 (Lisa y Tim) son cónyuges entre sí, y así sucesivamente. Podemos añadir el nombre y el apellido del cónyuge a cada registro de la tabla customer tabla. Para ello, debemos realizar un self join, es decir, unir la tabla customer tabla a sí misma:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.spouse_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust 
join customer spouse
on cust.spouse_id = spouse.customer_id;

Al ejecutar este código, el resultado es el siguiente:

Ahora que has visto un ejemplo de uso de las autouniones, vamos a revisar su sintaxis SQL.

Sintaxis de las autouniones

La sintaxis del self join es muy similar a la de cualquier otro tipo de join. Aquí está el código de nuestro ejemplo de self join:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.spouse_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust
join customer spouse
on cust.spouse_id = spouse.customer_id;

Se trata de una sentencia JOIN en la que la tabla customer tabla se utiliza dos veces.

La información sobre el cónyuge, como su nombre, se almacena en la misma tabla como un cliente separado con su propio customer_id. Como spouse_id contiene el customer_id del cónyuge, necesitamos unir la tabla consigo misma para obtener el nombre del cónyuge.

Se puede pensar en una autounión como una unión entre dos copias de la misma tabla. Para cada registro con un valor no nulo en spouse_id, buscamos el valor de customer_id que coincida con él. Cuando encontramos una coincidencia, las columnas firstname y lastname se añaden a la tabla resultante.

Los alias de las tablas son necesarios en un self join. El código no funciona sin ellos, ya que no sabría a qué copia de la tabla se refiere. Aquí, estoy usando los alias cust y spouse.

Justo antes de la palabra clave FROM, elegimos las columnas que queremos mantener en la tabla resultante. Necesitamos utilizar los alias de la tabla para recuperar las columnas (cust.firstname, cust.lastname, spouse.firstname, etc.). Mantenemos cinco columnas de la tabla customer y añadimos de la misma tabla dos columnas que contienen el nombre del cónyuge.

Se trata de una unión interna, pero se puede utilizar cualquier tipo de unión: LEFT JOIN RIGHT JOIN , CROSS JOIN, etc. En nuestro ejemplo, tenemos una unión interna que devuelve sólo los registros coincidentes; es decir, sólo se devuelven los clientes con cónyuges. Donna no tiene cónyuge en la tabla, por lo que Donna no está incluida en el conjunto de datos resultante.

Si quieres aprender más sobre las uniones, te recomiendo nuestro artículo "Cómo aprender Tipos de JOIN en SQL." También hay un curso interactivo de SQL joins disponible en nuestra LearnSQL.es plataforma.

Ejemplos de autounión

Lo anterior es sólo un ejemplo. Una vez que se empieza a utilizar SQL en el día a día, se encuentra la necesidad de autouniones con bastante frecuencia.

Un caso común de uso de la autounión es cuando hay una jerarquía entre los registros de una tabla. Este tipo de estructura de datos se denomina estructura de árbol, y a menudo es necesario unir la tabla consigo misma en SQL. Aquí hay algunos ejemplos.

Ejemplo 1: Jerarquía de empleados

Cada empleado tiene un gerente, y un gerente a su vez tiene su gerente, todo en la misma tabla. Si quiere añadir la información del gerente correspondiente a cada registro, tiene que hacer un self join. Cubrimos este ejemplo en el artículo "Una Guía Ilustrada de la Autounión SQL", así que échale un vistazo para ver cómo se ve esto.

Ejemplo 2: Jerarquía de departamentos

Cada departamento dentro de una organización tiene un padre: por ejemplo, el departamento de Ciencia de Datos está bajo el departamento de TI, el departamento de TI está bajo el de Soporte de Negocios, y el de Soporte de Negocios está bajo la junta directiva.

Considere la siguiente tabla, departmentque tiene el siguiente aspecto:

Ahora, si quiere añadir el nombre del padre a cada departamento, tiene que escribir un self join:

select c.*,
   p.name as parent_name
from department c 
left join department p 
on c.parent_department_id=p.department_id;

Dentro de este SELECT, unimos la tabla department con ella misma para obtener el nombre del departamento padre como campo adicional. Observe que el registro cuyo department_id es 1 no tiene un padre (parent_department_id es NULL; no está poblado). Esto se debe a que el consejo de administración se encuentra en la parte superior de la estructura de árbol. Queremos mostrar este registro en el resultado, por lo que utilizamos un LEFT JOIN y no un INNER JOIN.

Cuando ejecute este código, la tabla resultante tendrá el siguiente aspecto:

Puede ver fácilmente el padre al que pertenece cada departamento: TI está bajo Administración, Administración está bajo el Consejo, etc.

Ejemplo 3: Jerarquía de categorías

Tomemos como ejemplo los anuncios clasificados, esas populares páginas web en las que se puede alquilar, comprar o vender cualquier cosa, desde inmuebles hasta productos y servicios diversos. Para publicar un anuncio, debe elegir una categoría y una subcategoría para su anuncio. Por ejemplo, si vende bienes inmuebles, deberá elegir entre subcategorías como casa, apartamento o terreno.

Tenemos una tabla llamada category que contiene información sobre estas categorías y subcategorías, así como sus relaciones. Las relaciones entre las categorías y subcategorías de esta tabla se almacenan en una estructura padre-hijo como ésta:

Todas las categorías y subcategorías están en esta tabla. Ahora, si quiere añadir información sobre el padre a cada registro, tendrá que hacer un self join-unir esta tabla a sí misma:

select subcategory.*,
    main.category_name as parent_name
from category subcategory 
left join category main 
on subcategory.parent_category_id = main.category_id;

Este es el resultado de la ejecución de esta sentencia SQL:

Relaciones múltiples entre dos tablas

Hay situaciones, además de la auto-unión, en las que se necesita unir la misma tabla más de una vez. Una de ellas es cuando se tienen múltiples relaciones entre dos tablas diferentes. En este caso, se une la misma tabla dos veces, pero normalmente a otra tabla y no necesariamente a sí misma.

Supongamos que la tabla customer tiene dos campos que contienen IDs de ciudades. Esto es habitual si se tienen dos ciudades diferentes para cada cliente... por ejemplo, la ciudad de residencia (residence_city_id) y la ciudad de la dirección postal donde se deben enviar las notificaciones (notice_city_id):

También tenemos city que tiene el ID de la ciudad (city_id) y el nombre de la ciudad (name), como se ha visto anteriormente y se muestra a continuación como recordatorio:

Ahora, si se quiere mostrar los nombres de las ciudades, habrá que unir la city tabla dos veces:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.residence_city_id,
      cust.notice_city_id,
      residence_city.name as residence_city_name,
      notice_city.name as notice_city_name
from customer cust 
join city residence_city 
on cust.residence_city_id=residence_city.city_id
join city notice_city 
on cust.notice_city_id=notice_city.city_id;

Vamos a desglosar lo que ocurre en este código. Primero, unimos customer y city con residence_city_id como clave. Obtenemos residence_city_name al hacerla coincidir con city_id en la city tabla. Se realiza una segunda unión entre customer y city para obtener notice_city_name. La clave utilizada aquí es notice_city_id, que también coincide con city_id en la tabla city tabla.

Utilizamos los alias de la tabla cust para customer, residence_city para la primera copia de city para obtener el nombre de la ciudad de residencia, y notice_city para la segunda copia de city para obtener el nombre de la ciudad de notice. Utilizamos los alias para definir las columnas de la tabla resultante. Los alias también se utilizan durante la unión para definir las columnas clave. De nuevo, los alias son necesarios para distinguir las dos copias de city.

Cuando se ejecuta este código, se obtiene el siguiente resultado:

Ahora tenemos dos columnas adicionales con los nombres de las ciudades correspondientes.

Aquí utilizamos uniones simples (es decir, internas), pero puedes utilizar cualquier tipo de unión según sea necesario. Si eres nuevo en el mundo de las uniones SQL y quieres leer más sobre sus diferentes tipos, te recomiendo los artículos "Cómo aprender las uniones" y "Cómo practicar las uniones" que cubren estos temas. Si prefieres aprender viendo vídeos. Te recomiendo el episodio que trata sobre las uniones.

Unir la misma tabla dos veces

En este artículo, discutimos cuando necesitas unir la misma tabla dos veces en SQL y vimos algunos casos de uso común en el negocio. Explicamos cómo hacerlo y cómo es la sintaxis de SQL. Las autouniones con datos jerárquicos y las relaciones múltiples entre dos tablas son sólo dos de las situaciones en las que se necesita unir la misma tabla dos veces. Hay otras; generalmente, implican añadir una o más columnas a un conjunto de resultados de la misma tabla en la misma columna.

Si quieres aprender más sobre las uniones, incluyendo las autouniones, te recomiendo nuestro curso interactivo SQL Joins disponible en nuestra LearnSQL.es plataforma. Cuando se trata de SQL, es importante practicar; ¡nuestro curso está diseñado precisamente para eso!