¿Se Pueden Utilizar Múltiples Sentencias WITH en SQL?

Una completa guía sobre las sentencias WITH múltiples en SQL, perfecta tanto para principiantes como para expertos.

La cláusula SQL WITH le permite definir una CTE (expresión común de tabla). Una CTE es como una tabla que se rellena durante la ejecución de la consulta. Puede utilizar varias sentencias WITH en una consulta SQL para definir varias CTE. En este artículo, explicaremos cómo definir múltiples CTEs en una sola consulta. También mostraremos cómo este enfoque lleva el poder expresivo de una consulta SQL al siguiente nivel.

¿Se Pueden Utilizar Múltiples Sentencias WITH en SQL?

Sí, a continuación se muestra una consulta simple que utiliza dos cláusulas WITH:

WITH wine_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘wine’
  AND qty_exported > 0
),
car_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘car’
  AND qty_exported > 0
)
SELECT country
FROM wine_exporting_country 
INTERSECT car_exporting_country

En la consulta anterior, hay dos CTEs. El primero (en rojo) se llama wine_exporting_country y el segundo (en azul) es car_exporting_country. La consulta principal (en negro) hace referencia a ambas sentencias WITH como tablas regulares utilizando INTERSECT. La intersección de las dos tablas devolverá sólo los países presentes en ambos resultados CTE.

Observe que la palabra clave WITH sólo aparece una vez. Después de la cláusula WITH, debe poner el nombre del CTE, luego la palabra clave AS y finalmente entre paréntesis la consulta SELECT para definir el CTE.

Si quieres aprender a utilizar varias sentencias WITH en una misma consulta, te recomiendo nuestro curso interactivo Consultas recursivas y expresiones de tabla comunes interactivo. Te enseña los detalles de la sintaxis de la cláusula WITH en más de 100 ejercicios prácticos. Cubre la sintaxis básica, múltiples sentencias WITH, y consultas recursivas - el uso más complejo de la sintaxis WITH.

La cláusula WITH en SQL

Expliquemos primero cómo funciona la cláusula WITH. Crea una especie de tabla virtual (la CTE) sobre la marcha, que se crea y rellena durante la ejecución de una única sentencia SQL; después de la ejecución SQL, la 'tabla' CTE se elimina automáticamente. Para una mejor comprensión de la cláusula WITH, sugiero el artículo ¿Qué es una CTE?

En este artículo, utilizaremos una tabla de base de datos llamada product que realiza un seguimiento de los países y los productos más populares que producen (por ejemplo, aceite de oliva, vino, coches). Registra la cantidad producida, importada y exportada por cada país. La tabla también contiene el precio del producto y la población del país.

Veamos un ejemplo sencillo de consulta que utiliza una cláusula WITH para definir un CTE. Supongamos que queremos obtener una lista de los países que exportan más aceite de oliva del que importan. Queremos los resultados ordenados por la cantidad producida por cada país. Esta es la consulta:

WITH olive_oil_exporter AS (
  SELECT 
    country, 
    qty_produced
  FROM product
  WHERE product_name = ‘olive oil’
  AND qty_exported > qty_imported
)
SELECT country
FROM olive_oil_exporter
ORDER BY qty_produced DESC;

En esta consulta, tenemos un CTE llamado olive_oil_exporter, que es como una tabla con dos columnas: country y qty_produced. Las filas del CTE sólo contienen los países que exportan más aceite de oliva del que importan. A continuación, utilizamos una sentencia regular SELECT para consultar el CTE olive_oil_exporter en busca de los nombres de los países, que ordenamos de forma descendente según la cantidad de aceite de oliva producida en ese país.

Uso de varias sentencias WITH en SQL

Supongamos que queremos clasificar los países en países productores de alimentos y países productores de tecnología. Para ser productor de alimentos, un país debe exportar más de 100 millones de dólares en productos alimenticios. Para ser productor de alta tecnología, un país debe producir más de 1.000 dólares en productos tecnológicos por habitante.

Queremos un informe con los nombres de todos los países y dos columnas llamadas is_a_food_producer y is_a_hightech_producer. La consulta SQL para este informe es

WITH food_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘food’
  GROUP BY country
  HAVING SUM( qty_exported * unit_price) > 100000000
)
hightech_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘technology’
  GROUP BY country
  HAVING SUM( qty_produced / country_population) > 1000
)
SELECT DISTINCT 
  p.country,
  CASE 
     WHEN fp.country IS NULL THEN ‘No’ 
     ELSE ‘Yes’ END AS is_a_food_produced,
  CASE 
    WHEN htp.country IS NULL THEN ‘No’ 
    ELSE ‘Yes’ END AS is_a_hightech_produced,
FROM products p
LEFT JOIN food_producer fp 
ON fp.country = p.country
LEFT JOIN hightech_producer htp 
ON htp.country = p.country

En la consulta anterior, podemos identificar claramente tres consultas separadas. Las dos primeras utilizan la cláusula WITH para definir dos tablas CTE: food_producer y hightech_producer. La tercera consulta es la consulta principal, que consume las dos tablas CTE creadas anteriormente.

Después de la cláusula WITH, se puede ver el nombre de la tabla CTE (food_producer), luego la subcláusula AS y finalmente (entre paréntesis) la consulta para ese CTE. Para el segundo CTE, la cláusula WITH no es necesaria; basta con poner una coma, y luego repetir la misma sintaxis empezando por el nombre del CTE.

Los dos CTE food_producer y hightech_producer sólo tienen una columna: country. La consulta principal toma los nombres de todos los países de la tabla producty, a continuación, se realiza una LEFT JOIN con cada uno de los CTE. Cuando el LEFT JOIN no tiene una fila coincidente, significa que la respuesta para este país es "No"; cuando hay una fila coincidente, el valor para este país es "Sí".

Antes de pasar a la siguiente sección, me gustaría sugerirte el artículo Cómo escribir múltiples CTEs. En él puedes encontrar muchos ejemplos y explicaciones sobre CTEs.

Uso de Sentencias Múltiples y Anidadas WITH en SQL

En algunos casos, necesitamos una segunda CTE basada en la primera CTE: una CTE anidada. En otras palabras, la consulta para definir la segunda CTE debe tener una referencia a la primera CTE. Veamos un ejemplo.

Queremos obtener el importe total en dólares exportado por países en productos alimenticios. En el mismo informe, queremos mostrar el porcentaje que representa este importe en el total de exportaciones de ese país para todo tipo de productos. La consulta es:

WITH country_export_by_product AS (
  SELECT country, 
  product_family, 
  SUM(qty_exported * unit_price) AS total_exports
  FROM product
  GROUP BY country, product_family
),
country_export_total AS (
  SELECT country, 
  SUM(total_exports) AS total_exports_country
  FROM country_export_by_product 
  GROUP BY country
)
SELECT 
  cp.country, 
  product_family, 
  cp.total_exports_food ,
  ROUND((cp.total_exports_food / ct.total_exports_country) * 100, 2) 
     AS percentage_of_total_exports
FROM country_export_by_product cp
JOIN country_export_total ct 
ON ct.country = cp.country
ORDER BY country, product_family;

En esta consulta, creamos un CTE llamado country_export_by_product que tiene las columnas country, product_family y total_exports (que representa el importe total de este producto exportado por este país (en dólares)). Observe que la cláusula GROUP BY utiliza las columnas country y product_family.

El siguiente CTE se llama country_export_total y se basa en el CTE anterior country_export_by_product. La idea de este CTE es obtener la cantidad total exportada por cada país basándose en el CTE anterior. Obsérvese que en el segundo CTE utilizamos una cláusula de país GROUP BY. La necesidad de distintos niveles de la cláusula GROUP BY es la razón por la que tenemos dos CTE.

La consulta principal hace referencia a ambos CTE, uniéndolos por el valor del país. A continuación, la expresión

TRUNC((cp.total_exports_food / ct.total_exports_country) * 100, 2)

... se utiliza para calcular el porcentaje que representa cada producto en las exportaciones totales de ese país.

En términos de sintaxis, puede hacer que un CTE haga referencia a otro CTE en la misma consulta. Esto es lo que hemos hecho en nuestra consulta: al definir el CTE country_export_total, hemos hecho referencia al CTE country_export_by_product definido anteriormente.

Observe que podemos hacer referencia a la sentencia WITH definida antes de la sentencia WITH actual, pero no a las posteriores. Puede hacer referencia a cada sentencia WITH varias veces en otra sentencia WITH o en una consulta principal. En nuestro ejemplo, hemos hecho referencia a la primera sentencia WITH definida (la sentencia country_export_by_product CTE) en dos lugares: En el segundo WITH (el CTE country_export_total ) y en la consulta principal.

Otras limitaciones relacionadas con la sintaxis de la cláusula WITH son:

• Debe utilizar la palabra clave WITH sólo una vez, antes de la primera CTE.
• Todos los CTEs están separados por comas, pero no hay coma antes de la consulta principal. Esto sigue el patrón sintáctico:

            WITH cte_name1 AS (query1), 
           cte_name2 AS (query2) 
      main_query

Sugiero el artículo Explicación de las CTEs de SQL con ejemplos para varios ejemplos más de consultas WITH; demuestran cómo mejorar la organización y legibilidad de sus consultas SQL utilizando CTEs.

Uso de la Cláusula WITH para Crear Consultas recursivas y expresiones de tabla comunes

La cláusula WITH se utiliza en SQL para definir consultas recursivas. Las consultas recursivas le ayudan a consultar estructuras jerárquicas (por ejemplo, organigramas, árboles o gráficos). Puede obtener más información sobre la consulta de estructuras jerárquicas aquí.

Las consultas recursivas se basan en la cláusula WITH. Para crear una consulta recursiva, sólo necesita una cláusula WITH, pero la consulta dentro de WITH consta de dos partes.

Las consultas recursivas son útiles cuando las tablas o el modelo de datos de la base de datos tienen una especie de jerarquía implícita. Quizá la tabla de ejemplo más común para explicar este tema sea la típica tabla employee con las columnas employee_id y manager_employee_id.

Si queremos que un informe muestre todos los empleados con los nombres de sus jefes y el nivel jerárquico del empleado, podemos utilizar la siguiente consulta recursiva:

WITH RECURSIVE company_hierarchy AS (
  SELECT 
    employee_id, 
    firstname, 
    lastname, 
    manager_employee_id, 
    0 AS hierarchy_level
  FROM employees
  WHERE manager_employee_id IS NULL 
  UNION ALL 
  SELECT 
    e.employee_id, 
    e.firstname, 
    e.lastname, 
    e.manager_employee_id,
    hierarchy_level + 1
  FROM employees e, company_hierarchy ch
  WHERE e.manager_employee_id = ch.employee_id
)
SELECT 
  ch.firstname AS employee_first_name, 
  ch.lastname AS employee_last_name,
  e.firstname AS boss_first_name, 
  e.lastname AS boss_last_name,
  hierarchy_level
FROM company_hierarchy ch
LEFT JOIN employees e 
ON ch.manager_employee_id = e.employee_id
ORDER BY ch.hierarchy_level, ch.manager_employee_id;

En esta consulta, podemos ver la cláusula WITH RECURSIVE, que se utiliza para crear un CTE recursivo llamado company_hierarchy. El CTE tendrá todos los nombres de los empleados con los nombres de sus gerentes. Observe que el CTE tiene dos sentencias SELECT conectadas por UNION ALL. La primera SELECT es para obtener el primer empleado en la consulta recursiva (CEO John Smith).

La segunda SELECT de UNION es una consulta que se ejecuta varias veces. En cada ejecución, devuelve el empleado o empleados del siguiente nivel en la jerarquía. Por ejemplo, su primera ejecución devuelve todos los empleados que dependen directamente de John Smith.

Por último, hay una tercera sentencia SELECT; está fuera del CTE. Selecciona los nombres de los empleados, los nombres de sus jefes y el nivel jerárquico. Los datos se toman del CTE y se unen con la tabla employees. Utilizamos LEFT JOIN porque queremos todos los datos del CTE (incluido John Smith, que tiene un valor NULL en la columna manager_id). Los resultados se muestran en orden ascendente: primero por el nivel jerárquico, luego por el del jefe employee_id. A continuación se muestra el resultado de la consulta:

Para obtener más información sobre las consultas recursivas, consulte el artículo ¿Qué es una CTE recursiva en SQL?

Continúe Aprendiendo Acerca de las Sentencias Múltiples WITH en SQL

En este artículo, cubrimos el uso de múltiples sentencias WITH en una sola consulta SQL. También mencionamos como utilizar la cláusula WITH en consultas recursivas. Si desea continuar aprendiendo sobre la cláusula WITH, le recomiendo nuestro curso Consultas recursivas y expresiones de tabla comunes curso, que ofrece una excelente oportunidad para practicar el tipo más desafiante de consultas SQL.

Las sentencias WITH múltiples son más útiles cuando se escriben informes SQL complejos. Si ése es tu caso, también te recomiendo nuestra hoja gratuita SQL for Data Analysis Cheat Sheet, que hemos diseñado específicamente para ayudarte a escribir consultas complejas para el análisis de datos.

Si quieres practicar SQL a un nivel avanzado, echa un vistazo a nuestra pista de prácticas SQL avanzado. Contiene más de 200 ejercicios que le ayudarán a practicar conceptos avanzados de SQL. Cada dos meses, publicamos un nuevo curso práctico de SQL avanzado en nuestra pista mensual Ejercicio de SQL.

También puede obtener todos estos cursos y más en nuestro Todo, para siempre plan. El plan te da acceso de por vida a todos nuestros cursos de SQL en varios niveles de competencia y en cuatro dialectos de SQL. Inscríbase hoy mismo.