Un CTE es una expresión de tabla temporal definida al inicio de una consulta SQL, utilizando la sentencia WITH. Sirve para organizar, estructurar o reutilizar bloques de lógica SQL compleja, evitando subconsultas anidadas o repetidas. Es útil para mejorar la claridad, mantenimiento y, en algunos casos, el rendimiento de tus queries.

Sintaxis de ejemplo

WITH cte_name AS (
  -- Aquí va un SELECT que define la "tabla temporal"
  SELECT ...
  FROM ...
  WHERE ...
)
SELECT *
FROM cte_name
WHERE ...;

Incluso puedes usar varios para reutilizar

WITH cte1 AS (...),
     cte2 AS (...)
SELECT ...
FROM cte2
JOIN cte1 ON ...

Hagamos un ejemplos con tabla DEPT y EMP

-- Tabla DEPT
CREATE TABLE DEPT (
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME  VARCHAR2(14),
  LOC    VARCHAR2(13)
);

-- Inserts DEPT
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
-- Tabla EMP
CREATE TABLE EMP (
  EMPNO    NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);

-- Inserts EMP
INSERT INTO EMP VALUES (7839, 'KING',    'PRESIDENT', NULL,      TO_DATE('17-11-1981', 'DD-MM-YYYY'), 5000, NULL,    10);
INSERT INTO EMP VALUES (7566, 'JONES',   'MANAGER',   7839,      TO_DATE('02-04-1981', 'DD-MM-YYYY'), 2975, NULL,    20);
INSERT INTO EMP VALUES (7698, 'BLAKE',   'MANAGER',   7839,      TO_DATE('01-05-1981', 'DD-MM-YYYY'), 2850, NULL,    30);
INSERT INTO EMP VALUES (7782, 'CLARK',   'MANAGER',   7839,      TO_DATE('09-06-1981', 'DD-MM-YYYY'), 2450, NULL,    10);
INSERT INTO EMP VALUES (7788, 'SCOTT',   'ANALYST',   7566,      TO_DATE('19-04-1987', 'DD-MM-YYYY'), 3000, NULL,    20);
INSERT INTO EMP VALUES (7902, 'FORD',    'ANALYST',   7566,      TO_DATE('03-12-1981', 'DD-MM-YYYY'), 3000, NULL,    20);
INSERT INTO EMP VALUES (7844, 'TURNER',  'SALESMAN',  7698,      TO_DATE('08-09-1981', 'DD-MM-YYYY'), 1500, 0,       30);
INSERT INTO EMP VALUES (7900, 'JAMES',   'CLERK',     7698,      TO_DATE('03-12-1981', 'DD-MM-YYYY'), 950,  NULL,    30);
INSERT INTO EMP VALUES (7654, 'MARTIN',  'SALESMAN',  7698,      TO_DATE('28-09-1981', 'DD-MM-YYYY'), 1250, 1400,    30);
INSERT INTO EMP VALUES (7499, 'ALLEN',   'SALESMAN',  7698,      TO_DATE('20-02-1981', 'DD-MM-YYYY'), 1600, 300,     30);
INSERT INTO EMP VALUES (7521, 'WARD',    'SALESMAN',  7698,      TO_DATE('22-02-1981', 'DD-MM-YYYY'), 1250, 500,     30);
INSERT INTO EMP VALUES (7934, 'MILLER',  'CLERK',     7782,      TO_DATE('23-01-1982', 'DD-MM-YYYY'), 1300, NULL,    10);
INSERT INTO EMP VALUES (7876, 'ADAMS',   'CLERK',     7788,      TO_DATE('23-05-1987', 'DD-MM-YYYY'), 1100, NULL,    20);
INSERT INTO EMP VALUES (7369, 'SMITH',   'CLERK',     7902,      TO_DATE('17-12-1980', 'DD-MM-YYYY'), 800,  NULL,    20);

Primer ejemplo, obtener a los empleados con salario mayor al promedio. El WITH empleados_con_sueldo_alto viene siendo un resultado como si de una tabla temporal se tratara.

WITH empleados_con_sueldo_alto AS (
  SELECT *
  FROM emp
  WHERE sal > (SELECT AVG(sal) FROM emp)
)
SELECT ename, sal
FROM empleados_con_sueldo_alto;

Segundo ejemplo CTE recursivo (ideal para jerarquías). No solo con un connect by 👽👽

WITH jerarquia_empleados (empno, ename, mgr, nivel) AS (
  SELECT empno, ename, mgr, 1 AS nivel
  FROM emp
  WHERE mgr IS NULL
  UNION ALL
  -- Recursivo: busca los empleados de los jefes previos
  SELECT e.empno, e.ename, e.mgr, je.nivel + 1
  FROM emp e
  JOIN jerarquia_empleados je ON e.mgr = je.empno
)
SELECT *
FROM jerarquia_empleados
ORDER BY nivel, ename;

Usos frecuentes de los CTE

• Reemplazar subconsultas repetitivas.
• Hacer código más legible y mantenible.
• Implementar lógica recursiva (como estructuras jerárquicas, árboles, cadenas de dependencias).
• Encadenar transformaciones de datos intermedios.
• Modularizar lógica de reporting compleja.

Buenas prácticas

• Nombra bien tus CTE; deben indicar claramente qué hacen.
• Usa varios CTE para pasos intermedios en pipelines complejos.
• Revisa el plan de ejecución; a veces un CTE se materializa y puede impactar el rendimiento si se abusa.

Resumen

• ¿La consulta crece demasiado o se vuelve ilegible? Usa CTE.
• ¿Subconsultas repetidas? Usa CTE.
• ¿Necesitas recursividad o estructuras jerárquicas? Usa CTE.