Autounión de tablas en PostgreSQL

Cuando trabajas con modelos relacionales, no siempre necesitas cruzar información entre dos tablas distintas. En ocasiones, lo que necesitas es comparar datos dentro de una misma tabla. En estos casos, lo que se utiliza es una autounión de tablas en PostgreSQL, también conocida como self join.

En este artículo voy a explicarte qué es una autounión, cuándo se utiliza y cómo puedes implementarla en PostgreSQL. Primero veremos la sintaxis básica y después repasaremos varios ejemplos con casos reales y estructuras sencillas para que lo entiendas al vuelo.

Consejo

Al mostrar columnas de varias tablas, en el caso de que exista la misma columna en varias tablas dará error. Siempre se aconseja utilizar alias de tablas para evitar este problema.

Modelo utilizado

Al final del artículo puedes encontrar el script completo para crear la tabla utilizada en este ejemplo. Para poder comprender el funcionamiento es necesario que visualices el modelo que está construido en la base de datos. Tenemos una tabla de empleados donde la columna id_manager está referenciada al id_empleado de su misma tabla.

Autojoin

El autojoin no es más que una relación de una tabla consigo misma. En el modelo creado podemos ver como la tabla de empleados tiene un id de empleado y un id de manager, si las relacionamos conseguir obtener el nombre de un empleado y quien es su jefe. Este tipo de uniones se combina con inner, left, right o full outer join. Vamos a verlo con un ejemplo:

Sintaxis

La sintaxis para realizar una autounion es la siguiente:

SELECT tabla1.columna1, tabla1.columnaN, 
       tabla2.columna1, tabla2.columnaN 
FROM nombreTabla1 tabla1
   [INNER, LEFT, RIGHT, FULL] JOIN nombreTabla1 tabla2 
   ON tabla1.columna1 = tabla2.columna2

Ejemplo ¿quién es el jefe?

Si queremos mostrar a los empleados y quienes son sus jefes realizaremos esta consulta:

select mng.nombre JEFE, emp.nombre EMPLEADO
from empleados mng
  inner join empleados emp
  on mng.id_empleado = emp.id_manager
  order by mng.nombre;

Si ejecutamos la consulta comprobamos como José es jefe de Ismael, Pilar es jefa de Miguel y José.

Ejemplo ¿quién no tiene jefe?

Si queremos mostrar a los empleados y quienes son sus jefes pero además ver quien no tiene jefe, realizaremos esta consulta:

select mng.nombre JEFE, emp.nombre EMPLEADO
from empleados mng
  right join empleados emp
  on mng.id_empleado = emp.id_manager
  order by mng.nombre;

Si ejecutamos la consulta comprobamos como José es jefe de Ismael, Pilar es jefa de Miguel y José, pero Pilar no tiene ningún jefe.

Ejemplo completo

Copia y pega el siguiente código en tu consola de PostgreSQL y realiza paso a paso leyendo los comentarios.

--MiguelTroyano.com
-- Borramos las tablas si existen
drop table if exists empleados;

-- Creamos las tablas
create table empleados
(
     id_empleado numeric(2,0) primary key,
     nombre VARCHAR (50),
     sueldo integer,
     id_manager numeric(2,0),
     foreign key (id_manager)
	  references empleados(id_empleado)
	  on delete cascade
);

-- Insertamos valores
insert into empleados
values (1,'Miguel Troyano',60000,4),
       (2,'Ismael Troyano',60000,3),
       (3,'Jose Troyano',80000,4),
       (4,'Pilar Redondo',80000,null);

-- Mostramos los empleados con sus jefes
select mng.nombre JEFE, emp.nombre EMPLEADO
from empleados mng
  inner join empleados emp
  on mng.id_empleado = emp.id_manager
  order by mng.nombre;

-- Mostramos los empleados con sus jefes
-- y ademas, quien no tiene jefe
select mng.nombre JEFE, emp.nombre EMPLEADO
from empleados mng
  right join empleados emp
  on mng.id_empleado = emp.id_manager
  order by mng.nombre;