CTE Common table expresion: simplifica y facilita la construcción de consultas complejas mediante el uso de WITH.

Nos permite definir consultas auxiliares para su reutilización en consultas más complejas.

Permiten dividir consultas complejas en consultas más pequeñas y legibles.

Beneficios del uso de CTE:

• Facilita la legibilidad y mantenimento del código.
• Generar y reutilizar código.
• Construir consultas recursivas.

Ejemplo de una consulta compleja, donde se muestra cada diferencia respecto a la maximo, minimo y medio de precios de cada uno de los productos carnicos:

SELECT 
  prod.product_name,
  prod.price - (SELECT MAX(prod2.price) FROM ventas.tb_product prod2 WHERE category_id = 'C002') AS diferencia_max,
  prod.price - (SELECT MIN(prod3.price) FROM ventas.tb_product prod3 WHERE category_id = 'C002') AS diferencia_min,
  prod.price - (SELECT AVG(prod4.price) FROM ventas.tb_product prod4 WHERE category_id = 'C002') AS diferencia_avg    
FROM
  ventas.tb_product prod
  
WHERE
  category_id = 'C002'
  
ORDER BY
  product_id

Ejemplo de consulta compleja usando CTE:

WITH precios AS (
    SELECT 
      MAX(price) AS max_price,
      MIN(price) AS min_price,
      AVG(price) AS avg_price
    
    FROM 
      ventas.tb_product
    
    WHERE
      category_id = 'C002'
)

SELECT 
  product_name,
  price - (SELECT max_price FROM precios) AS diferencia_max,
  price - (SELECT min_price FROM precios) AS diferencia_min,
  price - (SELECT avg_price FROM precios) AS diferencia_avg    
FROM
  ventas.tb_product
  
WHERE
  category_id = 'C002'
  
ORDER BY
  product_id