Cómo unir una tabla a sí misma con Self Join de SQL Server

Resumen: en este tutorial, aprenderás a utilizar Self Join de SQL Server para unir una tabla a sí misma.

Sintaxis de self join de SQL Server

Self join permite unir una tabla a sí misma. Es útil para consultar datos jerárquicos o comparar filas dentro de la misma tabla.

Self Join utiliza la cláusula inner join o left join. Debido a que la consulta que usa Self Join hace referencia a la misma tabla, el alias de la tabla se usa para asignar diferentes nombres a la misma tabla dentro de una consulta.

Ten en cuenta que hacer referencia a la misma tabla más de una vez en una consulta sin usar alias de tabla resultará en un error.

A continuación, se muestra un SELECT con la sintaxis de unir la tabla T consigo misma:

SELECT
    select_list
FROM
    T t1
[INNER | LEFT]  JOIN T t2 ON
    join_predicate;

La consulta hace referencia a la tabla T dos veces. Los alias de tabla t1 y t2 se utilizan para asignar a la tabla T diferentes nombres en la consulta.

Ejemplos de Self Join de SQL Server

Tomemos algunos ejemplos para comprender cómo funciona Self Join.

1) Uso de self join para consultar datos jerárquicos

Considera la siguiente tabla de staffs (personal) de la base de datos de muestra:

Datos en la tabla:

La tabla de personal almacena la información del personal, como identificación, nombre, apellido y correo electrónico. También tiene una columna llamada manager_id que especifica el jefe directo. Por ejemplo, Mireya le informa a Fabiola porque el valor en el manager_id de Mireya es Fabiola.

Fabiola no tiene jefe, por lo que la columna de identificación del administrador tiene un NULL.

Para obtener quién informa a quién, utilizaremos Self Join como se muestra en la siguiente consulta:

SELECT
    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    sales.staffs e
INNER JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
    manager;

Resultado:

En este ejemplo, hicimos referencia a la tabla de personal dos veces: una como “e” para los empleados y la otra como “m” para los administradores. El predicado de unión coincide con la relación de empleado y administrador utilizando los valores de las columnas e.manager_id y m.staff_id.

La columna de empleados no tiene a Fabiola Jackson debido al efecto INNER JOIN. Si reemplazas la cláusula INNER JOIN por la cláusula LEFT JOIN como se muestra en la siguiente consulta, obtendrás el conjunto de resultados que incluye a Fabiola Jackson en la columna de empleado:

SELECT
    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    sales.staffs e
LEFT JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
    manager;

Resultado:

2) Uso de self join para comparar filas dentro de una tabla

Consulta la siguiente tabla de clientes:

La siguiente declaración utiliza self join para encontrar los clientes que se ubican en la misma ciudad.

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id
AND c1.city = c2.city
ORDER BY
    city,
    customer_1,
    customer_2;

Resultado:

La siguiente condición asegura que la declaración no compare al mismo cliente:

c1.customer_id > c2.customer_id

Y la siguiente condición coincide con la ciudad de los dos clientes:

AND c1.city = c2.city

Ten en cuenta que si cambias el operador mayor que (>) por el operador diferente a (<>), obtendrás más filas:

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
AND c1.city = c2.city
ORDER BY
    city,
    customer_1,
    customer_2;

Resultado:

Veamos la diferencia entre mayor que > y diferente que <> en la cláusula ON limitando a una ciudad para facilitar la comparación.

La siguiente consulta devuelve los clientes que se encuentran en Albany:

SELECT
   customer_id, first_name + ' ' + last_name c,
   city
FROM
   sales.customers
WHERE
   city = 'Albany'
ORDER BY
   c;

Resultado:

Esta consulta usa el operador (>) en la cláusula ON:

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id
AND c1.city = c2.city
WHERE c1.city = 'Albany'
ORDER BY
    c1.city,
    customer_1,
    customer_2;

Resultado:

Esta consulta usa el operador (<>) en la cláusula ON:

SELECT
    c1.city,
        c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
AND c1.city = c2.city
WHERE c1.city = 'Albany'
ORDER BY
        c1.city,
    customer_1,
    customer_2;

Resultado:

En este tutorial, has aprendido a utilizar Self Join de SQL Server para consultar datos jerárquicos y comparar filas en una misma tabla.