Factorizando consultas en Oracle con la cláusula WITH

En más ocasiones de las que nos gustaría, nos enfrentamos a consultas SQL muy complejas que acceden a varias tablas y en las que, incluso, se puede llegar a repetir el acceso a una misma tabla.

Para poder simplificar estas consultas existe la cláusula WITH, con la que podemos dar nombre a las consultas SELECT que después utilizaremos cuantas veces sea necesario. Definiremos tantas consultas como vayamos a necesitar posteriormente, separadas por comas, y cada una de ellas es visible para todas las que vayamos definiendo a continuación dentro del mismo WITH.

Veamos algunos ejemplos prácticos, y comenzamos con uno sencillo que recupera de una BD el nombre y la población de todos sus clientes.

1.- Utilizando subselects:

SELECT
   CLIENTES.ID,
   COALESCE (CLIENTES.RAZONSOCIAL, CLIENTES.NOMBRE || ' ' || CLIENTES.APELLIDOS) AS nombre_cliente,
   (SELECT POBLACIONES1.LOC_NOMBRE
    FROM POBLACIONES POBLACIONES1
    LEFT JOIN CLIENTES cli1 ON cli1.LOCALIDAD_ID = POBLACIONES1.LOC_ID
    WHERE cli1.ID = CLIENTES.ID
   ) AS poblacion_cliente,
   (SELECT POBLACIONES2.LOC_NOMBRE
    FROM POBLACIONES POBLACIONES2
    LEFT JOIN CLIENTES CLIENTES2 ON CLIENTES2.LOCALIDAD_ID_DIRECCION = POBLACIONES2.LOC_ID
    WHERE
    CLIENTES2.ID = CLIENTES.ID
   ) AS poblacion_postal
FROM CLIENTES;

2. Utilizando la cláusula WITH

WITH localidades AS (
   SELECT LOC_ID AS id, LOC_NOMBRE AS nombre
   FROM POBLACIONES
)
SELECT
   COALESCE (pc.RAZONSOCIAL, pc.NOMBRE || ' ' || pc.APELLIDOS) AS nombre_cliente,
   pob1.nombre AS poblacion_cliente, pob2.nombre AS poblacion_postal
FROM CLIENTES pc
LEFT JOIN localidades pob1 ON pob1.id = pc.LOCALIDAD_ID
LEFT JOIN localidades pob2 ON pob2.id = pc.LOCALIDAD_ID_DIRECCION;

Como se puede ver, en el segundo ejemplo hemos dejado “guardada” la consulta que recupera el nombre de las localidades. Y la hemos llamado una vez por cada localidad que hemos necesitado. Hemos conseguido simplificar mucho el código, haciéndolo más comprensible.

Leyendo artículos sobre el funcionamiento de WITH, en algunos se hace mención a que los tiempos de ejecución mejoran utilizándola, en otros se dice que no mejoran absolutamente nada, y hasta hay una tercera vertiente que afirma que aumentan. Mi experiencia personal, ejecutando consultas complejas y que devuelven cantidades notables de registros, es que los tiempos de ejecución son prácticamente los mismos: no hay diferencia.

Pero este artículo no trata sobre rendimientos ni planes de ejecución, sino sobre la claridad del código, y de cómo podemos lograr que la persona que acceda a nuestro código de forma evolutiva logre comprender muy rápidamente lo que la consulta recupera de BD. Resumiendo, habremos logrado mejorar la lectura y el mantenimiento del código y, por lo tanto, el coste de producción del proyecto disminuirá al haber mejorado su tiempo de desarrollo.

Vamos a ver ahora otro ejemplo práctico, que tiene un mayor grado de complejidad, para comprobar cómo una consulta “dura” de leer se vuelve sencilla.

Suponiendo que tenemos una tabla de bonos de formación, vamos a buscar el nombre y saldo que les queda a los los clientes que  tenían saldo en el año 2022 y que no tengan contratos desde hace 4 meses (el cliente no ha firmado ni ha dado de baja contratos en los últimos 4 meses).

1. Por medio de subqueries:

SELECT
  CLIENTE_ID, NUMERO_CLIENTE, ASIGNADA, PENDIENTE, ASIGNADA - PENDIENTE,
  COALESCE (RAZONSOCIAL, NOMBRE || ' ' || APELLIDOS) AS NOMBRE, CIF
FROM
(
  SELECT *
  FROM
  (
    SELECT *
    FROM
    (
      SELECT *
      FROM BONOSFORMACION b
      INNER JOIN CLIENTES pc ON b.CLIENTE_ID = pc.ID
      WHERE b.ANIO = 2022 AND b.PENDIENTE > 0
    ) aux_bonos
  ) aux_cliente_completo
  LEFT JOIN 
    (
      SELECT DISTINCT CLIENT_ID
      FROM OFERTAS
      WHERE FECHAFIRMA IS NULL
      OR FECHAFIRMA <= ADD_MONTHS(TO_DATE(SYSDATE, 'DD/MM/YYYY'),-4)
      OR FECHABORRADOREGISTRO <= ADD_MONTHS(TO_DATE(SYSDATE, 'DD/MM/YYYY'),-4)
    ) aux_ofertas ON aux_cliente_completo.CLIENTE_ID = aux_ofertas.CLIENTE_ID
) aux
ORDER BY CLIENTE_ID;

2. Mediante el uso de WITH:

WITH aux_ofertas AS (
  SELECT DISTINCT CLIENT_ID
  FROM OFERTAS
  WHERE FECHAFIRMA IS NULL
  OR FECHAFIRMA <= ADD_MONTHS(TO_DATE(SYSDATE, 'DD/MM/YYYY'),-4)
  OR FECHABORRADOREGISTRO <= ADD_MONTHS(TO_DATE(SYSDATE, 'DD/MM/YYYY'),-4) 
),
aux_bonos AS (
  SELECT *
  FROM BONOSFORMACION b
  INNER JOIN CLIENTES pc ON b.CLIENTE_ID = pc.ID
  WHERE b.ANIO = 2022 AND b.PENDIENTE > 0 
),
aux AS (
  SELECT *
  FROM aux_bonos
  LEFT JOIN aux_ofertas ON aux_bonos.CLIENTE_ID = aux_ofertas.CLIENTE_ID 
)
SELECT CLIENTE_ID, NUMERO_CLIENTE, ASIGNADA, PENDIENTE, ASIGNADA - PENDIENTE,
  COALESCE (RAZONSOCIAL, NOMBRE || ' ' || APELLIDOS) AS NOMBRE, CIF
FROM aux
ORDER BY CLIENTE_ID;

¿Cuál de las dos opciones pensáis que es más sencilla de leer y poder modificar? 😉