Sobre bases de datos, hace muchos años que tengo pendiente escribir algo del Modelo de Conjuntos Anidados (The Nested Set Model) como forma de guardar una estructura de árbol en la tabla de una base de datos relacional. Uno uso práctico habitual es guardar un menú de opciones. Antes de ello, mejor introducir la relación reflexiva (o recursiva) de una tabla consigo misma.

En una tabla así, uno de sus campos hace referencia a la clave primaria, de tal modo que puede establecerse una relación de jerarquía. Es recomendable crear un clave foránea que apunte desde ese campo a la clave primaria. A continuación, veamos un ejemplo en el que el campo «cap» (jefe) de la tabla de empleados apunta al identificador del empleado:

Este es su conjunto de datos para el ejemplo:

Esta consulta devuelve los jefes, mostrando su nombre y número de empleado, y los empleados que tiene cada uno. De los empleados muestra todos sus atributos (e.* hace referencia a todos los campos de la tabla):

SELECT c.nom as cap, c.numemp as numcap, e.*
FROM empleats AS c JOIN empleats AS e ON c.numemp = e.cap
ORDER BY c.nom;

Debe prestarse atención en el orden en el que aparecen los campos para hacer el join, si se intercambian de este modo:

SELECT c.nom AS cap, c.numemp AS numcap, e.*
FROM empleats AS c LEFT JOIN empleats AS e ON c.cap = e.numemp 
ORDER BY c.nom;

La tortilla se ha girado, el SELECT no da la misma información que antes, pues ahora los campos «e» representan los jefes:

Para obtener los jefes y sus empleados, tal y como estamos haciendo desde el principio, la siguiente consulta con LEFT JOIN no da lo buscado:

SELECT c.nom as cap, c.numemp as numcap, e.*
FROM empleats AS c LEFT JOIN empleats AS e ON c.numemp = e.cap
ORDER BY c.nom;

La que se ha solicitado aquí es tratar a todos los empleados como jefes, si lo son se mostrarán sus empleados; en el caso de ser empleados «rasos» todos los atributos de su empleado aparecen nulos, pues carecen de los mismos.

Ahora bien, esto no significa que una consulta OUTER, LEFT o RIGHT, no pueda ser útil en una relación reflexiva. Por ejemplo, si se desea un listado de todos los empleados y su jefe al lado, debe emplearse un OUTER para conseguir que también aparezcan los empleados sin jefe directo, como es el caso del director general:

SELECT e.nom as empleat, e.numemp as numempleat, c.*
FROM empleats AS e LEFT JOIN empleats AS c ON c.numemp = e.cap
ORDER BY c.nom;