Una autounión es una unión regular que une una tabla consigo misma. En la práctica, se suele utilizar una autounión para consultar datos jerárquicos o para comparar filas dentro de la misma tabla.

Para formar una autounión, especifique la misma tabla dos veces con alias de tablas diferentes y proporcione el predicado de unión después de la palabra clave ON.

La siguiente consulta utiliza un INNER JOIN que une la tabla consigo misma:

SELECT select_list
FROM table_name t1
INNER JOIN table_name t2 ON join_predicate;

En esta sintaxis, nombre_tabla se une a sí misma mediante la cláusula INNER JOIN.

Alternativamente, puede utilizar la cláusula LEFT JOIN o RIGHT JOIN para unir la tabla a sí misma de la siguiente manera:

SELECT select_list
FROM table_name t1
LEFT JOIN table_name t2 ON join_predicate;

Ejemplos de auto-uniones en PostgreSQL¶

Veamos algunos ejemplos de uso de autouniones.

Ejemplo de consulta de datos jerárquicos¶

Vamos a crear una tabla de ejemplo para la demostración.

Supongamos que tenemos la siguiente estructura organizativa:

Las siguientes sentencias crean la tabla employee e insertan algunos datos de ejemplo en la tabla.

CREATE TABLE employee (
  employee_id INT PRIMARY KEY, 
  first_name VARCHAR (255) NOT NULL, 
  last_name VARCHAR (255) NOT NULL, 
  manager_id INT, 
  FOREIGN KEY (manager_id) REFERENCES employee (employee_id) ON DELETE CASCADE
);
INSERT INTO employee (employee_id, first_name, last_name, manager_id) 
VALUES 
  (1, 'Windy', 'Hays', NULL), 
  (2, 'Ava', 'Christensen', 1), 
  (3, 'Hassan', 'Conner', 1), 
  (4, 'Anna', 'Reeves', 2), 
  (5, 'Sau', 'Norman', 2), 
  (6, 'Kelsie', 'Hays', 3), 
  (7, 'Tory', 'Goff', 3), 
  (8, 'Salley', 'Lester', 3);

SELECT * FROM employee;

Salida:

 employee_id | first_name |  last_name  | manager_id
-------------+------------+-------------+------------
           1 | Windy      | Hays        |       null
           2 | Ava        | Christensen |          1
           3 | Hassan     | Conner      |          1
           4 | Anna       | Reeves      |          2
           5 | Sau        | Norman      |          2
           6 | Kelsie     | Hays        |          3
           7 | Tory       | Goff        |          3
           8 | Salley     | Lester      |          3
(8 rows)

En esta tabla employee, la columna manager_id hace referencia a la columna employee_id.

La columna manager_id indica la relación directa, mostrando el manager del que depende el empleado.

Si la columna manager_id contiene NULL, significa que el empleado en cuestión no depende de nadie, ocupando esencialmente el puesto de alto directivo.

La siguiente consulta utiliza la autounión para averiguar quién depende de quién:

SELECT 
  e.first_name || ' ' || e.last_name employee, 
  m.first_name || ' ' || m.last_name manager 
FROM 
  employee e 
  INNER JOIN employee m ON m.employee_id = e.manager_id 
ORDER BY 
  manager;

Salida:

    employee     |     manager
-----------------+-----------------
 Sau Norman      | Ava Christensen
 Anna Reeves     | Ava Christensen
 Salley Lester   | Hassan Conner
 Kelsie Hays     | Hassan Conner
 Tory Goff       | Hassan Conner
 Ava Christensen | Windy Hays
 Hassan Conner   | Windy Hays
(7 rows)

Esta consulta hace referencia a la tabla employees dos veces, una como empleado y otra como directivo. Utiliza los alias de tabla e para el empleado y m para el directivo.

El predicado join encuentra la pareja empleado/director comparando los valores de las columnas employee_id y manager_id.

Observe que el máximo responsable no aparece en el resultado.

Para incluir al máximo responsable en el conjunto de resultados, utilice la cláusula LEFT JOIN en lugar de INNER JOIN como se muestra en la siguiente consulta:

SELECT 
  e.first_name || ' ' || e.last_name employee, 
  m.first_name || ' ' || m.last_name manager 
FROM 
  employee e 
  LEFT JOIN employee m ON m.employee_id = e.manager_id 
ORDER BY 
  manager;

Salida:

    employee     |     manager
-----------------+-----------------
 Anna Reeves     | Ava Christensen
 Sau Norman      | Ava Christensen
 Salley Lester   | Hassan Conner
 Kelsie Hays     | Hassan Conner
 Tory Goff       | Hassan Conner
 Hassan Conner   | Windy Hays
 Ava Christensen | Windy Hays
 Windy Hays      | null
(8 rows)

Comparación de las filas con la misma tabla¶

Véase la siguiente tabla film de la base de datos de alquiler de DVD:

La siguiente consulta encuentra todos los pares de películas que tienen la misma duración,

SELECT 
  f1.title, 
  f2.title, 
  f1.length 
FROM 
  film f1 
  INNER JOIN film f2 ON f1.film_id > f2.film_id 
  AND f1.length = f2.length;

Salida:

           title           |            title            | length
---------------------------+-----------------------------+--------
 Chamber Italian           | Affair Prejudice            |    117
 Grosse Wonderful          | Doors President             |     49
 Bright Encounters         | Bedazzled Married           |     73
 Date Speed                | Crow Grease                 |    104
 Annie Identity            | Academy Dinosaur            |     86
 Anything Savannah         | Alone Trip                  |     82
 Apache Divine             | Anaconda Confessions        |     92
 Arabia Dogma              | Airplane Sierra             |     62
 Dying Maker               | Antitrust Tomatoes          |    168
...

El predicado join coincide con dos películas diferentes (f1.film_id > f2.film_id) que tienen la misma longitud (f1.length = f2.length)

Resumen¶

• Una autounión PostgreSQL es una unión regular que une una tabla a sí misma usando INNER JOIN o LEFT JOIN.
• Las autouniones son muy útiles para consultar datos jerárquicos o comparar filas dentro de la misma tabla.