Sentencia WITH para simplicar SQL complejos

Las consultas SQL complejas que usan subquerys pueden ejecutarse de manera mas rapida con el uso de tablas globales temporales.

Desde las versiones de Oracle 9i R2 ya se implemento una utilidad que ayuda a simplificar las subconsultas complejas con el comando WITH. El comando WITH es una utilidad que materializa las subconsultas(Genera una temporal)

Consideraciones:

• Se recomienda su uso si la subconsulta se ejecuta varias veces
• Se crea automaticamente una temporal
• No funciona en consultas recursivas

Estructura:
WITH
   nommbre_subconsulta
AS
  (El sql que se materializará)
SELECT
  (SQL en el que se usará la subconsulta);

 

Veamos el siguiente ejemplo:



Modelo ER

 Tenemos la tabla de clientes y la tabla de ventas, deseamos obtener por cliente el total vendido, cantidad de facuras, total de montos pendientes, cantidad de facturas pendientes y que el promedio de ventas sea mayor a 15000.

SQL implementado con subconsultas
Select C.Cliente_Id,C.Nombre_Completo,
(Select Sum(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id) Monto_Ventas,
(Select Count(*) From  Ventas V Where V.Cliente_Id=C.Cliente_Id) Cantidad_Ventas,
(Select Avg(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id) Promedio_Ventas,
(Select Sum(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id And Pagada='N') Monto_Pendiente,
(Select Count(*) From  Ventas V Where V.Cliente_Id=C.Cliente_Id And Pagada='N') Cantidad_Pendiente
From Clientes C
Where (Select Avg(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id)>15000;



Tiempo y resultado de la consulta



Ahora vemos el ejemplo con la clausula WITH

With T_Ventas --Nombre de la subconsulta
As( --Seccion de la subconsulta
    Select  V.Cliente_Id,
            Sum(V.Capital)  Monto_Ventas,
            Count(*)        Cantidad_Ventas,
            Avg(V.Capital)  Promedio_Ventas,
            Sum(V.Capital)  Monto_Pendiente,
            Count(*)        Cantidad_Pendiente   
    From Ventas V
    Group By V.Cliente_Id
)
--Sección en donde hacemos uso de la subconsulta
Select  C.Cliente_Id,
        C.Nombre_Completo,
        Vt.Monto_Ventas,
        Vt.Cantidad_Ventas,
        Vt.Promedio_Ventas,
        Vt.Monto_Pendiente,
Vt.Cantidad_Pendiente
From Clientes C
Inner Join T_Ventas Vt On C.Cliente_Id=Vt.Cliente_Id
Where Vt.Promedio_Ventas>15000;

Tiempo y resultado de la consulta con clausula WITH

Lo único nuevo es el uso de la cláusula WITH por que de ahí el join y condición con T_Ventas se maneja como si fuera una tabla.

Conculsión:
Con la introducción de la cláusula WITH simplificamos el uso de consultas SQL que usan subconsultas, ademas de que los tiempos de respuesta de la consulta son mejores con WITH que con el uso de subsonsultas.