Cómo Obtener los Descendientes de un Padre en SQL

¿Quieres aprender a manejar árboles genealógicos y encontrar descendientes de un padre? Al leer este artículo, aprenderás a manejar datos jerárquicos.

Encontrar descendientes de un padre es un problema común en SQL. Si te imaginas un árbol genealógico, el bloque básico que forma las relaciones dentro de él es la relación padre-hijo. La relación padre-hijo es precisamente lo que define todos los datos jerárquicos.

Otro ejemplo de datos jerárquicos es la relación gerente-empleado. Se pueden encontrar datos jerárquicos en muchas otras situaciones, como los datos detallados sobre los proyectos, sus subproyectos y todas las tareas que se pueden dividir en subtareas. Otro ejemplo de datos jerárquicos es cuando construyes una clasificación de seres vivos en la que clasificas las criaturas según el reino, el filo, las clases, el orden, las familias, el género y la especie. O tal vez se trate de un género musical y sus subgéneros. Por ejemplo, el blues puede dividirse en subgéneros como el Delta blues, el country blues, el jump blues y el Chicago blues. Esto influyó en un nuevo estilo, la música soul. La música soul tiene sus subgéneros, como el Southern soul, el Motown soul, el Philly soul, el progressive soul, etc., que a su vez influyeron en la creación de otros géneros, como el funk o la música disco.

Este artículo cubre los temas que puedes aprender con más detalle en el curso Consultas recursivas y expresiones de tabla comunes . Hay una guía que describe cómo estos conocimientos pueden ser beneficiosos para ti; su lectura puede ayudarte a decidir si aprendes las consultas recursivas.

Diseño de una tabla jerárquica

Observará una característica específica de las tablas que contienen datos con relaciones padre-hijo: dos columnas que contienen valores de ID. Una columna es el ID utilizado para la identificación única de los datos dentro de la tabla. La otra columna contiene el ID utilizado para referenciar otra fila.

Déjame mostrarte lo que quiero decir con esto. La tabla que vas a utilizar se llama family_treecon las siguientes columnas:

• id: El ID de la persona y la clave primaria (PK) de la tabla.
• first_name: El nombre de la persona.
• last_name: El apellido de la persona.
• parent_id: El ID del padre de la persona.

Estos son algunos datos de la tabla:

Se ve que el ID de John McArthur es 2. El ID de Sam McArthur es 5, mientras que su parent_id = 2. Esto significa que su padre tiene el ID 2; en este caso, es John McArthur.

Esta estructura de datos es característica de los datos jerárquicos, que son bastante comunes en las bases de datos. Para obtener datos de una tabla como ésta, tendrás que utilizar consultas jerárquicas o recursivas. Si quieres saber lo que pueden hacer las consultas recursivas, salta al artículo que revela su poder. Seré más específico y te mostraré cómo estructurar una consulta recursiva para obtener todos los descendientes de un padre de la tabla anterior.

Consulta recursiva: Recuperación de los descendientes

En primer lugar, te mostraré el aspecto de la consulta. Luego la desglosaré para ti, analizaré todas las partes de la consulta y explicaré su propósito.

Vamos allá. La consulta que te dará todos los descendientes de un padre es esta:

WITH RECURSIVE descendant AS (
	SELECT	id,
			first_name,
			last_name,
			parent_id,
			0 AS level
	FROM family_tree
	WHERE id = 1

	UNION ALL

	SELECT	ft.id,
			ft.first_name,
			ft.last_name,
			ft.parent_id,
			level + 1
	FROM family_tree ft
JOIN descendant d
ON ft.parent_id = d.id
)

SELECT	d.id AS descendant_id,
		d.first_name AS descendant_first_name,
		d.last_name AS descendant_last_name,
		a.id AS ancestor_id,
		a.first_name AS ancestor_first_name,
		a.last_name AS ancestor_last_name,
		d.level
FROM descendant d
JOIN family_tree a
ON d.parent_id = a.id
ORDER BY level, ancestor_id;

La consulta comienza definiendo la CTE. Las tres letras significan Expresión de Tabla Común, y reconocerás una CTE cuando veas la palabra WITH. En otro artículo se explica la teoría de la CTE y su sintaxis.

Las CTEs pueden ser recursivas o no recursivas. ¿Te preguntas cómo diferenciarlas? Es más sencillo de lo que crees; los que empiezan por WITH RECURSIVE son recursivos.

Cuando escribes una CTE, tienes que especificar su nombre. En mi caso, el CTE se llama descendiente mediante la siguiente línea de código: WITH RECURSIVE descendiente AS. Lo que sigue después es una sentencia SELECT entre paréntesis, que se almacena como CTE. En esta sentencia SELECT, selecciono todas las columnas de la tabla family_tree. También añado una nueva columna llamada level que contendrá el valor 0. Tengan paciencia; verán el propósito de este paso en un momento. Por último, utilizo una cláusula WHERE para filtrar los resultados. Me interesa encontrar todos los descendientes de un tal Peter McArthur, cuyo id = 1.

A continuación, "fusiono" esta sentencia SELECT con la siguiente utilizando UNION ALL. Se trata de un comando que permite combinar los resultados de dos o más sentencias SELECT. Para que UNION ALL funcione, todas las sentencias SELECT deben tener el mismo número de columnas. La siguiente sentencia SELECT vuelve a seleccionar todas las columnas de la tabla family_tree. Además, toma el valor de la columna level (de la sentencia SELECT anterior, 0) y añade 1 con cada recursión. Los datos de esta sentencia SELECT se obtienen uniendo la tabla family_tree con la CTE, tratada aquí como cualquier otra tabla. Como los datos del CTE son los mismos que los de la tabla family_treesalvo por la nueva columna level, este JOIN esencialmente une la tabla family_tree con ella misma en las columnas parent_id y id.

Cerrando los paréntesis, ha terminado de definir el CTE. Ahora viene la sentencia SELECT, que utiliza el CTE y devuelve todos los descendientes de cada padre. Primero selecciono las columnas id, first_name, y last_name del descendiente del CTE en esta sentencia SELECT. El CTE se trata como una tabla normal, con d como alias. La uno con la tabla family_tree con el alias a. He elegido este alias, porque estoy tratando el CTE como la tabla para los datos descendientes y family_tree como la tabla que contiene los datos del ancestro. Es un simple truco que me permite no meter la pata al escribir una consulta. Las columnas seleccionadas de la tabla family_tree son de nuevo id, first_name, y last_name. La última columna seleccionada es level del CTE.

Al final, los datos se ordenan por las columnas level y ancestor_id. Éste es el resultado:

Estos son todos los descendientes de Peter McArthur cuyo ID es 1. Las personas en el Nivel 1 son los hijos de Peter McArthur. Sus nietos están en el Nivel 2, con sus padres mostrados en la tabla. El Nivel 3 muestra los bisnietos de Peter McArthur, con sus padres también mostrados por sus nombres.

Si sabe cómo escribir la consulta anterior, puede aplicarla a cualquier otro escenario en el que haya una estructura de datos jerárquica.

Consejos adicionales

Escritura de CTEs recursivos en Microsoft SQL Server

Si intentas ejecutar la consulta anterior en SQL Server, aparecerá un error. No hay que asustarse; SQL Server admite CTEs y consultas recursivas. La única diferencia es que no es necesario escribir RECURSIVE en SQL Server para obtener una consulta recursiva. Basta con omitirlo, así:

WITH descendant AS...

Todo lo demás es igual; no es necesario cambiar ninguna otra parte de la consulta. Si estás usando Oracle, aquí está el artículo que explica el uso de consultas jerárquicas.

Obtención de los valores nulos en la tabla

Si echa un vistazo a la tabla que muestra los descendientes de Peter McArthur, se dará cuenta de que no hay ningún Peter McArthur. No se trata de un error, sino simplemente de que no es su propio descendiente. Sin embargo, tal vez quiera mostrarlo a él también, para tener el árbol genealógico completo desde el primer antepasado hasta el último descendiente.

Para ello, primero tendrá que saber por qué Pedro no aparece en la tabla resultante. La razón es que he utilizado JOIN para conectar las tablas. Como Peter McArthur es el punto de partida del árbol genealógico, hay un valor NULL en la columna parent_id. El JOIN no devuelve los valores de NULL.

Sólo hay que cambiar una pequeña cosa en la consulta para obtener los valores de NULL. En lugar de utilizar JOIN, simplemente utilice LEFT JOIN. Esta es la parte del código que cambia; todo lo demás permanece igual:

...FROM descendant d LEFT JOIN family_tree a ON d.parent_id = a.id...

El resultado difiere en una fila, la primera:

¿Consiguiendo encontrar los descendientes de un padre?

En este artículo, has aprendido sobre la estructura de datos jerárquica y sobre la relación padre-hijo. Te he dado varios ejemplos de la vida real en los que puedes encontrar este tipo de estructuras de datos. No son los únicos, por supuesto.

Has aprendido la estructura característica de esos datos, como preparación para escribir una consulta que te permita obtener todos los descendientes de un ancestro. A partir de ese ejemplo, deberías ser capaz de aplicar los conocimientos en escenarios similares. Para dominar aún más la escritura de consultas recursivas y reconocer cuándo utilizarlas, es muy recomendable practicar con el curso Consultas recursivas y expresiones de tabla comunes . El artículo que explica cuándo usar los CTEs también puede ayudarte a mejorar con los CTEs.