Cuando trabajamos con consultas SQL complejas en Oracle, a menudo tenemos que repetir subconsultas o escribir expresiones largas que hacen que el código sea difícil de leer y mantener.
Para resolver esto, Oracle nos ofrece la cláusula WITH, también conocida como subquery factoring.
La cláusula WITH permite definir subconsultas temporales que luego pueden ser utilizadas dentro de una consulta principal.
Es como declarar variables en programación: escribimos la subconsulta una sola vez, le damos un nombre, y la reutilizamos en la consulta final.
Veamos un ejemplo básico
Supongamos que tenemos una tabla ventas con las columnas:
- id
- producto
- cantidad
- precio
Queremos obtener el total de ventas por producto y luego filtrar aquellos productos con ventas mayores a 1000.
WITH ventas_totales AS (
SELECT producto,
SUM(cantidad * precio) AS total
FROM ventas
GROUP BY producto
)
SELECT producto, total
FROM ventas_totales
WHERE total > 1000;
1. En la parte del WITH definimos ventas_totales como una subconsulta.
2. Luego usamos ventas_totales como si fuera una tabla en la consulta principal.
Las ventajas son:
- Hace que las consultas largas sean más legibles.
- Evita duplicar subconsultas.
- Permite estructurar consultas de forma modular.
Podemos definir más de una subconsulta en el mismo WITH:
WITH
ventas_totales AS (
SELECT producto, SUM(cantidad * precio) AS total
FROM ventas
GROUP BY producto
),
productos_top AS (
SELECT producto
FROM ventas_totales
WHERE total > 1000
)
SELECT p.producto, v.total
FROM productos_top p
JOIN ventas_totales v ON p.producto = v.producto;
Aquí usamos dos subconsultas (ventas_totales y productos_top) y las combinamos en la consulta final.
Desde Oracle 11g, la cláusula WITH también soporta consultas recursivas, muy útiles para recorrer jerarquías (como empleados y jefes, o categorías anidadas).
Ejemplo:
WITH empleados_recursivo (id, nombre, manager_id, nivel) AS (
SELECT id, nombre, manager_id, 1
FROM empleados
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.nombre, e.manager_id, er.nivel + 1
FROM empleados e
JOIN empleados_recursivo er ON e.manager_id = er.id
)
SELECT *
FROM empleados_recursivo;
Esto nos da una vista jerárquica de empleados y sus jefes, similar a CONNECT BY, pero más flexible.
Conclusión:
La cláusula WITH en Oracle es una herramienta poderosa para:
- Hacer más claras las consultas complejas.
- Reutilizar resultados intermedios.
- Explorar jerarquías con recursividad.
Cuando tus consultas SQL se vuelvan largas o difíciles de leer, pensá en usar WITH para organizarlas mejor.
