Cómo Consultar un Árbol Padre-Hijo en SQL

¿Qué son las estructuras de árbol padre-hijo en SQL? En este artículo, responderemos a esta pregunta, hablaremos sobre la jerarquía de consultas y mostraremos las cinco consultas SQL más comunes que necesitarás para estas estructuras de datos.

Sí, puedes usar SQL en una estructura de árbol padre-hijo. En este artículo te mostraré cómo. Durante el proceso, te guiaré a través de cinco ejemplos de consultas, empezando por las más sencillas y terminando por las más complejas. En ellos se utilizarán expresiones de tabla comunes (CTE) recursivas.

Si no está familiarizado con las CTE, le recomiendo nuestro curso interactivo Consultas recursivas y expresiones de tabla comunes interactivo. Contiene más de 100 ejercicios que te enseñan a usar las CTEs en SQL, empezando por lo básico y progresando hasta temas avanzados como las CTEs recursivas.

Antes de profundizar en el código, veamos un resumen de la estructura de árbol padre-hijo y cómo se almacena en una base de datos relacional.

¿Qué es un árbol padre-hijo?

Si entiendes de datos jerárquicos, probablemente sepas que es un sinónimo de estructura padre-hijo. Ambos nombres son muy lógicos; una estructura de árbol padre-hijo es un conjunto de datos estructurados jerárquicamente. En otras palabras, existen relaciones jerárquicas entre los elementos de datos. Esto significa que un elemento de datos puede ser el padre de otro elemento de datos, que pasa a llamarse hijo. Los elementos también se denominan niveles de árbol o nodos, y pueden adoptar tres formas principales:

• Nodo raíz - Es el primer nodo, donde comienza el árbol padre-hijo.
• Nodo padre - Es cualquier nodo que tiene uno o más nodos descendientes (o hijos).
• Nodo hijo - Cualquier nodo que tenga un nodo predecesor o padre.

Ejemplos reales de estructuras padre-hijo son las estructuras organizativas de las empresas (una empresa está formada por departamentos, los departamentos por equipos y los equipos por empleados), los árboles genealógicos (hay padres, hijos, nietos, bisnietos, etc.) y las taxonomías naturales (los seres vivos pertenecen a un dominio, reino, filo, clase, orden, familia, género y especie). Incluso las carpetas de ordenador (disco C, Archivos de Programa, Microsoft SQL Server...) , los menús (bebidas, bebidas sin alcohol, té...), los géneros artísticos y musicales (por ejemplo, existió el blues, que desarrolló el rhythm and blues, que dio lugar al soul, funk, etc.), y los proyectos (un proyecto tiene subproyectos, que a su vez tienen tareas, subtareas, etc.) pueden considerarse estructuras jerárquicas de datos.

Estructura de árbol padre-hijo en bases de datos relacionales

Para que SQL pueda hacer algo con ella, una estructura de árbol padre-hijo tiene que estar almacenada en una base de datos relacional.

Estas estructuras suelen almacenarse en una tabla con dos columnas de ID, de las cuales una hace referencia al ID de un objeto padre. Eso nos permite determinar la jerarquía entre los datos. En otras palabras, sabemos qué nodo es padre de qué nodo hijo y viceversa.

Esto puede sonar un poco abstracto, así que te mostraré cómo funciona con un ejemplo sencillo. Y lo haré literalmente. Mi estructura de árbol padre-hijo mostrará datos sobre los padres y sus hijos. Echa un vistazo:

Aquí, la columna id muestra el ID del hijo. Para averiguar quién es el padre de ese niño, tienes que mirar la columna parent_id, encontrar el mismo número de ID en la columna id, y buscar en esa fila el nombre del padre.

En otras palabras, Jim Cliffy no tiene padres en esta tabla; el valor de su columna parent_id es NULL. Esto significa que él es el nodo raíz de esta estructura de árbol.

Mark Cliffy es el hijo de Jim Cliffy. ¿Cómo lo sé? Porque Mark es parent_id = 1, que es el ID de Jim Cliffy. Mark Cliffy es un nodo hijo, pero también es un nodo padre. ¿Por qué? Porque Veronica Cliffy es la hija de Mark Cliffy. Lo sé porque su padre tiene parent_id = 2, y la tabla me dice que es Mark Cliffy. Veronica Cliffy es estrictamente un nodo hijo; tiene un nodo padre, pero de él no se ramifica ningún nodo hijo.

Consultas típicas en una estructura de árbol padre-hijo

Utilizaré la misma tabla para cada una de estas consultas. Tiene las mismas columnas que se muestran arriba, sólo que con más filas y diferentes valores en ellas.

Presentación de los datos de ejemplo

La tabla se llama parent_child y tiene las siguientes columnas:

• id - ID del niño y clave primaria de la tabla (PK).
• first_name - Nombre del niño.
• last_name - Apellido del niño.
• parent_id - ID del padre del niño.

Esta es la tabla completa:

Puedes utilizar esta tabla para comprobar si las consultas que voy a mostrar devuelven el resultado correcto.

Ejemplo 1: Listar todos los hijos de 1 padre

Esta es la consulta más simple, así que la usaré para que te sientas más cómodo con la estructura de árbol. En este caso, quiero encontrar todos los hijos de un padre especificado. En este caso, me interesa encontrar todos los hijos de una persona llamada Marge Wellington, cuyo ID es 4.

Esta es la consulta:

SELECT first_name,
	 last_name
FROM parent_child
WHERE parent_id = 4;

Simplemente he seleccionado el nombre y el apellido de la tabla y he utilizado la cláusula WHERE para mostrar sólo las filas en las que hay un 4 en la columna parent_id.

El resultado muestra tres filas:

Me dice que Bobby, Sammy y Sarah Wellington son todos hijos de Marge Wellington. Echa un vistazo a la tabla original y verás que es cierto.

¡Esto era sólo un calentamiento! Pasemos al siguiente.

Ejemplo 2: Listar un Nodo Padre Para un Nodo Hijo

Ahora, la salida en el ejemplo anterior fue un poco, bueno, básica. He listado sólo los nombres de los hijos. Podría ser muy útil mostrar también el nombre del padre. Y eso es exactamente lo que voy a hacer. Voy a mostrar tanto el nombre como el apellido del hijo y del padre.

En lugar de buscar a los hijos de un padre, ahora buscaré a los padres del hijo. Quiero saber quién es el padre de Sam Francis Dijkstra. Además de los nombres, también quiero ver los ID.

La consulta para esto es:

SELECT child.id AS child_id,
	 child.first_name AS child_first_name,
	 child.last_name AS child_last_name,
	 parent.first_name AS parent_first_name,
	 parent.last_name AS parent_last_name,
	 parent.id AS parent_id
FROM parent_child child 
JOIN parent_child parent
  ON child.parent_id = parent.id
WHERE child.id = 11;

El concepto principal que estoy introduciendo aquí es el self-join. He dado el alias child a la tabla parent_child y la he unido consigo misma usando el alias parent alias. Al hacer esto, estoy actuando como si estuviera trabajando con dos tablas diferentes. Una contiene los datos sobre los hijos; por eso la he llamado child. La otra contiene los datos sobre los padres, por eso la he llamado parent.

Las columnas seleccionadas lo reflejan. Los nombres e ID de los hijos se seleccionan de la tabla "primero". Los nombres e ID de los padres se seleccionan de la "segunda" tabla. Las "tablas" se unen cuando parent_id es igual a id.

La tabla original me dice que el ID de Sam Francis Dijkstra es 11. He utilizado la cláusula WHERE para filtrar los datos y mostrar sólo el padre de Sam Francis. También puede utilizar la cláusula WHERE en las columnas child.first_name y child.last_name. He optado por filtrar los datos utilizando el ID porque la consulta es un poco más corta de esa manera.

Este es el resultado:

Muestra que la madre de Sam Francis es Mary Dijkstra, lo cual es cierto.

¿Todo claro hasta ahora? Muy bien. Continuamos.

Ejemplo 3: Obtener un número de generación (o nivel de árbol) para cada nodo

En este ejemplo, quiero listar cada persona de la tabla y mostrar a qué generación pertenece. ¿Cuál es el propósito de esto? Cuando obtenga esos datos, podré ver fácilmente quién pertenece a qué generación: padres, hijos, nietos, etc.

Para ello utilizaré un CTE, pero no un CTE al uso, sino un CTE recursivo. Si necesitas refrescar tus conocimientos sobre CTE, aquí tienes un artículo que explica qué es una CTE.

Esta es mi consulta:

WITH RECURSIVE generation AS (
	SELECT id,
		first_name,
		last_name,
		parent_id,
		0 AS generation_number
	FROM parent_child
	WHERE parent_id IS NULL

UNION ALL 

	SELECT child.id,
		child.first_name,
		child.last_name,
		child.parent_id,
		generation_number+1 AS generation_number
	FROM parent_child child
	JOIN generation g
	  ON g.id = child.parent_id
)

SELECT first_name,
	 last_name,
	 generation_number
FROM generation;

Como toda CTE recursiva, la mía comienza con dos palabras clave: WITH RECURSIVE. He dado un nombre a la generación CTE. En la primera sentencia SELECT, estoy seleccionando IDs y nombres. Además, hay una nueva columna llamada generation_number con un 0 para todas las filas donde parent_id = NULL. ¿Por qué NULL? Porque sé que la persona predecesora de todas las demás no tiene ningún padre en la tabla. Por lo tanto, el valor debe ser NULL.

Estoy utilizando UNION ALL para combinar el resultado de esta sentencia SELECT con la segunda, que será la responsable de la recursividad. Para que UNION ALL funcione, el número de columnas y los tipos de datos deben ser los mismos en ambas sentencias SELECT.

El miembro recursivo vuelve a seleccionar IDs y nombres. También está la columna generation_number con el valor generation_number+1. Con cada recursión, se añadirá 1 al valor anterior de esta columna. Como la consulta comienza con 0, la primera recursión dará como resultado un 1 en la columna generation_number, la segunda un 2, y así sucesivamente.

Para que todo esto funcione, he unido la tabla parent_child con el propio CTE donde id = parent_id. Se aplica el mismo principio que con las tablas autounidas: la tabla sirve como datos de los hijos, el CTE sirve como datos de los padres.

Después de escribir el CTE, tengo que utilizar sus datos. Para ello, he escrito una sencilla sentencia SELECT que devuelve los nombres y los números de generación del CTE. Muy bonito, ¿verdad?

Este es el resultado:

Con este resultado, veo que Rosa Wellington es el nodo raíz porque su número de generación es 0. Todas las personas con valor 1 son sus hijos, valor 2 son nietos y valor 3 son bisnietos. Si compruebas esto en la tabla de origen, verás que todo lo que he dicho es cierto.

Ejemplo 4: Listar todos los descendientes

Este ejemplo es una extensión del anterior. Quiero mostrarte cómo listar todos los descendientes de un padre y mostrar tanto el nombre de los padres como el de los hijos.

Esta es la consulta:

WITH RECURSIVE generation AS (
	SELECT id,
		 first_name,
		 last_name,
		 parent_id,
		 0 AS generation_number
	FROM parent_child
	WHERE parent_id IS NULL

UNION ALL 

	SELECT child.id,
		 child.first_name,
		 child.last_name,
		 child.parent_id,
		 generation_number+1 AS generation_number
	FROM parent_child child
	JOIN generation g
	  ON g.id = child.parent_id

)

SELECT	g.first_name AS child_first_name,
		g.last_name AS child_last_name,
		g.generation_number,
		parent.first_name AS parent_first_name,
		parent.last_name AS parent_last_name
FROM generation g
JOIN parent_child parent
ON g.parent_id = parent.id
ORDER BY generation_number; 

Si comparas esta consulta con la anterior, verás que la parte CTE es idéntica. No hace falta que lo repita.

Lo que es diferente es la sentencia SELECT que hace referencia al CTE. Pero aquí tampoco hay nuevos conceptos SQL. La consulta selecciona los nombres de los hijos y de los padres y su número de generación. Lo hice uniendo de nuevo el CTE con la tabla parent_child. El CTE contiene los datos de los hijos, mientras que la tabla contiene los datos de los padres. La última línea de código ordena el resultado por el número de generación.

La consulta devuelve exactamente lo que quería:

¿O no? Claro, muestra todos los hijos y el nombre de sus padres. Pero falta Rosa Wellington, el nodo raíz y matriarca de esta familia. Y no he aplicado ningún filtro para excluirla.

¿Qué ha pasado? En realidad, he aplicado un filtro utilizando JOIN en la última sentencia SELECT. Recuerde que JOIN sólo devuelve las filas coincidentes de las tablas unidas. Rosa Wellington falta porque no tiene datos sobre su padre; en su caso, no hay datos en los que id pueda coincidir con parent_id. Si quieres incluirla a ella también, utiliza LEFT JOIN en el último SELECT:

…
FROM generation g LEFT JOIN parent_child parent ON g.parent_id = parent.id
…

Y el resultado completo está aquí:

Si quieres saber más sobre esta compleja consulta, aquí tienes un artículo dedicado a este ejemplo.

Ejemplo 5: Generar una vista en árbol de datos jerárquicos

El último ejemplo es el más complejo, pero también el más divertido. O al menos lo es su resultado. Sería una pena consultar estructuras arborescentes sin poder mostrar los datos en forma de árbol.

La tarea aquí es mostrar cada persona de la tabla. Además, cada descendiente tiene que mostrarse de forma que sea gráficamente obvio de quién es hijo y a qué generación pertenece. Esta es una vista de árbol. Creo que es mejor que esperes hasta que llegue a la salida de la consulta para ver lo que quiero decir con eso.

Manos a la obra. De nuevo, el CTE recursivo salva el día:

WITH RECURSIVE tree_view AS (
	SELECT id,
		 parent_id,
		 first_name,
		 last_name,
		 0 AS level,
		 CAST(id AS varchar(50)) AS order_sequence
	FROM parent_child
	WHERE parent_id IS NULL
	
UNION ALL

	SELECT parent.id,
		 parent.parent_id,
		 parent.first_name,
		 parent.last_name,
		 level + 1 AS level,
		 CAST(order_sequence || '_' || CAST(parent.id AS VARCHAR (50)) AS VARCHAR(50)) AS order_sequence
	FROM parent_child parent
	JOIN tree_view tv
	  ON parent.parent_id = tv.id
)

SELECT 
   RIGHT('------------',level*3) || first_name || ' ' || last_name 
     AS parent_child_tree
FROM tree_view
ORDER BY order_sequence;

Ya sabes cómo funcionan las consultas recursivas. Esta vez la CTE se llama tree_view. La primera sentencia SELECT selecciona algunos datos de la tabla donde parent_id es NULL. Ahí está la columna level con el valor 0. Y he utilizado la función CAST() para cambiar el tipo de datos id a VARCHAR; ya verás por qué lo necesito.

Volvemos a utilizar UNION ALL para fusionar los resultados de dos consultas. La segunda sentencia SELECT vuelve a seleccionar algunos datos, con la tabla parent_child unida a la propia CTE. Lo importante es que con cada recursión, se añadirá 1 al nivel anterior. Además, el guión bajo y el valor de la columna id se añadirán con cada recursión. Necesito este pequeño truco porque usaré esta columna más adelante para ordenar la salida. De esta forma, mostraré la vista de árbol correctamente. Para que lo entiendas, aquí tienes una fila de la tabla:

El valor de la columna para Frank Wellington será 1_2_6. ¿Por qué? Porque Rosa, como primer nivel, obtiene el valor 1. Jon Wellington es su hijo; su ID pasa a order_sequence, que ahora se convierte en 1_2. Luego se añade el ID de Frank, que pasa a ser 1_2_6. Al hacer esto en toda la estructura jerárquica, obtengo la columna que puedo utilizar para mostrar la salida de la forma deseada.

Volvamos a la consulta. Para obtener el resultado, se necesita un SELECT que utiliza los datos CTE. Aquí estoy utilizando la función RIGHT(). Extrae un número especificado de caracteres de la derecha. En mi caso, extrae el número de guiones level*3 de cada nivel. También he concatenado estos guiones con el nombre y el apellido. El resultado se ordena por el order_sequence.

¿Estás listo para ver la vista de árbol? Aquí la tienes:

Esta sencilla representación gráfica muestra de forma bastante obvia los niveles generacionales y quién es quién en este árbol genealógico. Por el número de guiones, puedes ver fácilmente que Jon, Joni y Marge Wellington son hijos de Rosa. Mary Dijkstra, Frank, Jason, Sarah, Bobby y Sammy Wellington son los nietos de Rosa. También es fácil ver quiénes son sus padres. También puedes ver quiénes son los bisnietos, pero eso te lo dejo a ti.

Antes de terminar, me gustaría recomendarte este artículo sobre cómo consultar estructuras de árbol en Oracle.

La Consulta de Árboles Padre-Hijo Se Hace Más Interesante

Las estructuras de árbol padre-hijo son muy interesantes. Son un conjunto de datos completamente diferente al que se suele consultar en las bases de datos relacionales. Le he mostrado qué son estas estructuras jerárquicas y cómo se representan en una tabla.

Y lo que es más importante, te he mostrado cinco consultas que puedes utilizar para resolver algunos de los problemas más comunes relacionados con los datos jerárquicos. Como has visto, las CTEs y las CTEs recursivas son vitales para consultar árboles padre-hijo.

Seguro que ya te has encontrado con datos jerárquicos en tu trabajo. Probablemente te hayas dado cuenta de que tienes que equiparte con conocimientos detallados sobre consultas recursivas para enfrentarte a este tipo de datos. Tenemos un curso enConsultas recursivas y expresiones de tabla comunes que te guiará sistemáticamente a través de CTEs en general, consultas recursivas, y cómo funciona la consulta de datos jerárquicos y gráficos en SQL.

¡Buena suerte con el aprendizaje! Y siéntete libre de utilizar todas las consultas que te he mostrado y de adaptarlas a las necesidades de tu negocio.