Módulo 36: Operadores de conjuntos y CTE
Objetivos del módulo
- Combinar los resultados de varias consultas con
UNION,UNION ALL,INTERSECTyMINUS - Conocer
EXCEPTy los operadores de multiconjuntoINTERSECT ALL,MINUS ALLyEXCEPT ALL(Oracle 21c y posteriores) - Aplicar las reglas de compatibilidad: número y tipo de columnas, nombres,
ORDER BYfinal y tratamiento deNULL - Controlar la precedencia de los operadores con paréntesis
- Usar los operadores de conjuntos para tareas reales: listados combinados, filas de totales y comparación de tablas
- Escribir consultas legibles con la cláusula
WITH(CTE, subquery factoring): una o varias CTE encadenadas y reutilizadas - Dominar las CTE recursivas: jerarquías con nivel y ruta, árboles, generación de series de números y fechas
- Ordenar y proteger las consultas recursivas con
SEARCH DEPTH FIRST/BREADTH FIRSTyCYCLE - Conocer el constructor de valores de tabla
VALUESde Oracle 23ai
1. Operadores de conjuntos: la idea
📘 Concepto: Los operadores de conjuntos (set operators) combinan verticalmente los resultados de dos o más consultas: las filas de una consulta se juntan con las de otra. Un
JOINcombina tablas horizontalmente (añade columnas); un operador de conjuntos combina resultados verticalmente (añade, quita o cruza filas).
flowchart LR
subgraph JOIN["JOIN: une columnas"]
direction LR
A1["A (a1, a2)"] --- B1["B (b1, b2)"] --> R1["(a1, a2, b1, b2)"]
end
subgraph SET["Operador de conjuntos: une filas"]
direction TB
A2["Consulta 1 (x, y)"] --> R2["(x, y)<br/>filas de 1 y/o 2"]
B2["Consulta 2 (x, y)"] --> R2
end
Corresponden directamente a las operaciones del álgebra relacional (módulo 18) y de la teoría de conjuntos:
| SQL | Álgebra | Resultado | Duplicados |
|---|---|---|---|
UNION | R ∪ S | Filas que están en R, en S o en ambas | Elimina |
UNION ALL | (unión de multiconjuntos) | Todas las filas de R y todas las de S | Conserva |
INTERSECT | R ∩ S | Filas que están a la vez en R y en S | Elimina |
MINUS / EXCEPT | R − S | Filas de R que no están en S | Elimina |
INTERSECT ALL | (multiconjuntos) | Cada fila tantas veces como el mínimo de apariciones | Conserva |
MINUS ALL / EXCEPT ALL | (multiconjuntos) | Cada fila tantas veces como apariciones en R menos apariciones en S | Conserva |
flowchart LR
U["UNION<br/>A ∪ B<br/>todo lo de A y de B"] ~~~ I["INTERSECT<br/>A ∩ B<br/>lo común"] ~~~ M["MINUS<br/>A − B<br/>lo de A que no está en B"]
Piensa en dos listas de invitados a dos fiestas. UNION es la lista de todas las personas que han ido a alguna fiesta (cada una una vez); UNION ALL es la lista concatenada (quien fue a las dos aparece dos veces); INTERSECT son los que fueron a las dos; MINUS son los que fueron a la primera y no a la segunda.
2. UNION y UNION ALL
2.1. UNION: unión sin duplicados
Todas las ciudades en las que la empresa tiene presencia (clientes o departamentos):
SELECT ciudad AS lugar FROM clientes
UNION
SELECT localidad FROM departamentos
ORDER BY lugar;
LUGAR
-----------
Alicante
Barcelona
Bilbao
Granada
Madrid
Oviedo
Palma
Sevilla
Valencia
Valladolid
Zaragoza
11 filas seleccionadas.
Hay 14 clientes y 7 departamentos (21 filas en total), pero muchas ciudades se repiten. UNION elimina los duplicados, igual que haría un DISTINCT sobre el resultado combinado:
SELECT COUNT(*) AS filas_union_all
FROM (SELECT ciudad FROM clientes
UNION ALL
SELECT localidad FROM departamentos);
SELECT COUNT(*) AS filas_union
FROM (SELECT ciudad FROM clientes
UNION
SELECT localidad FROM departamentos);
FILAS_UNION_ALL
---------------
21
1 fila seleccionada.
FILAS_UNION
-----------
11
1 fila seleccionada.
2.2. UNION ALL: concatenar resultados
UNION ALL no elimina duplicados: simplemente pone las filas de la segunda consulta a continuación de las de la primera. Departamentos de los proyectos y de los directores:
SELECT dept_id FROM proyectos
UNION ALL
SELECT dept_id FROM empleados WHERE oficio = 'DIRECTOR'
ORDER BY 1;
DEPT_ID
----------
10
20
30
30
30
40
50
50
60
60
10 filas seleccionadas.
La misma consulta con UNION deja solo los valores distintos:
SELECT dept_id FROM proyectos
UNION
SELECT dept_id FROM empleados WHERE oficio = 'DIRECTOR'
ORDER BY 1;
DEPT_ID
----------
10
20
30
40
50
60
6 filas seleccionadas.
💡 Consejo: Usa
UNION ALLsiempre que sepas que no puede haber duplicados (por ejemplo, porque cada rama tiene un literal distinto que identifica su origen) o cuando quieras conservarlos.UNIONobliga a Oracle a ordenar o a usar una tabla hash para eliminar duplicados, lo que con millones de filas tiene un coste importante. ElegirUNION“por si acaso” es un error de rendimiento frecuente.
2.3. Listados combinados con una columna de origen
Un uso muy habitual: una agenda con empleados y clientes. Añadimos un literal que indica el tipo de cada fila:
SELECT 'EMPLEADO' AS tipo, nombre || ' ' || apellidos AS contacto, telefono
FROM empleados
WHERE dept_id = 20
UNION ALL
SELECT 'CLIENTE', nombre, telefono
FROM clientes
WHERE comercial_id = 110
ORDER BY tipo, contacto;
TIPO CONTACTO TELEFONO
--------- --------------------------- ---------------
CLIENTE Construcciones Levante S.A. 963456789
CLIENTE Diseños Creativos S.L. 932223344
CLIENTE Talleres Martín S.L. 961234567
EMPLEADO Carmen Ruiz Ortega 600100110
EMPLEADO David Torres Romero 600100111
EMPLEADO Elena Navarro Castro 600100112
EMPLEADO Lucía Romero Serrano 600100114
EMPLEADO Luis Martínez Ruiz 600100101
EMPLEADO Sergio Jiménez Vidal 600100113
9 filas seleccionadas.
Observa:
- Los alias (
tipo,contacto) se definen en la primera consulta; en la segunda no hacen falta. - La columna
tipohace imposible que una fila de empleado coincida con una de cliente, así queUNION ALLes correcto y más eficiente queUNION.
3. INTERSECT
INTERSECT devuelve las filas que aparecen en ambos resultados (sin duplicados). Clientes que han hecho pedidos en 2025 y también en 2026:
SELECT cliente_id FROM pedidos
WHERE fecha_pedido BETWEEN DATE '2025-01-01' AND DATE '2025-12-31'
INTERSECT
SELECT cliente_id FROM pedidos
WHERE fecha_pedido BETWEEN DATE '2026-01-01' AND DATE '2026-12-31'
ORDER BY 1;
CLIENTE_ID
----------
1001
1002
1005
1007
1008
1009
1010
1012
8 filas seleccionadas.
Cada rama puede devolver un cliente varias veces (1002 tiene dos pedidos en 2025), pero el resultado lo muestra una sola vez.
💡 Consejo: Cuidado con
BETWEENy las fechas:BETWEEN DATE '2025-01-01' AND DATE '2025-12-31'funciona aquí porquefecha_pedidono guarda hora. Si la columna tuviera horas, un pedido del 31/12/2025 a las 18:00 quedaría fuera. La forma robusta esfecha >= DATE '2025-01-01' AND fecha < DATE '2026-01-01', que usaremos a partir de ahora.
4. MINUS y EXCEPT
MINUS devuelve las filas de la primera consulta que no aparecen en la segunda. Clientes que compraron en 2025 pero no han vuelto en 2026:
SELECT cliente_id FROM pedidos
WHERE fecha_pedido BETWEEN DATE '2025-01-01' AND DATE '2025-12-31'
MINUS
SELECT cliente_id FROM pedidos
WHERE fecha_pedido BETWEEN DATE '2026-01-01' AND DATE '2026-12-31'
ORDER BY 1;
CLIENTE_ID
----------
1003
1004
1006
3 filas seleccionadas.
A diferencia de UNION e INTERSECT, MINUS no es conmutativo: A MINUS B no es lo mismo que B MINUS A.
Para mostrar más datos que los que se comparan, se usa el resultado del operador como subconsulta:
SELECT c.cliente_id, c.nombre AS cliente
FROM clientes c
WHERE c.cliente_id IN (SELECT cliente_id FROM pedidos
WHERE fecha_pedido < DATE '2026-01-01'
MINUS
SELECT cliente_id FROM pedidos
WHERE fecha_pedido >= DATE '2026-01-01');
CLIENTE_ID CLIENTE
---------- ---------------------------
1003 María José Herrera
1004 Academia Nova
1006 Antonio Ruiz Pérez
3 filas seleccionadas.
⚠️ Importante: Si comparas con
MINUScolumnas que no son la clave (por ejemplo, el nombre del cliente), dos clientes distintos con el mismo nombre se considerarían “la misma fila”. Compara siempre por la clave y añade los demás datos después, como en el ejemplo.
4.1. MINUS y los valores nulos
Los operadores de conjuntos comparan filas completas y, a diferencia de =, consideran iguales dos NULL (como hace DISTINCT o GROUP BY). Por eso no sufren la trampa de NOT IN del módulo 31:
SELECT dept_id FROM departamentos
MINUS
SELECT dept_id FROM empleados;
DEPT_ID
----------
70
1 fila seleccionada.
La segunda consulta contiene un NULL (Tomás no tiene departamento) y aun así el resultado es correcto. Y los empleados que no son jefes de nadie, que con NOT IN daban cero filas:
SELECT COUNT(*) AS no_son_jefes
FROM (SELECT emp_id FROM empleados
MINUS
SELECT jefe_id FROM empleados);
NO_SON_JEFES
------------
17
1 fila seleccionada.
Una prueba directa de que dos NULL “coinciden” en un operador de conjuntos: el jefe_id de Ana es NULL y el dept_id de Tomás también.
SELECT jefe_id FROM empleados WHERE emp_id = 100
INTERSECT
SELECT dept_id FROM empleados WHERE emp_id = 160;
JEFE_ID
----------
(nulo)
1 fila seleccionada.
La intersección devuelve una fila: la que contiene el NULL.
4.2. EXCEPT, el nombre estándar
MINUS es el nombre propio de Oracle. El estándar SQL (y PostgreSQL, SQL Server, MySQL 8…) usa EXCEPT. Desde Oracle 21c se admiten ambos:
SELECT dept_id FROM departamentos
EXCEPT
SELECT dept_id FROM empleados;
DEPT_ID
----------
70
1 fila seleccionada.
⚠️ Importante:
EXCEPTestá disponible desde Oracle 21c (y por tanto en 23ai/26ai). En Oracle 19c usaMINUS, que funciona en todas las versiones.
5. Operadores de multiconjuntos: INTERSECT ALL, MINUS ALL, EXCEPT ALL
Hasta Oracle 19c, solo UNION tenía versión ALL. Desde 21c existen también INTERSECT ALL, MINUS ALL y EXCEPT ALL, que trabajan con multiconjuntos (bags): tienen en cuenta cuántas veces aparece cada fila.
⚠️ Importante:
INTERSECT ALL,MINUS ALLyEXCEPT ALLestán disponibles desde Oracle 21c. En 19c no existen y hay que simularlos con funciones analíticas (ROW_NUMBER, módulo 33).
Partamos de dos multiconjuntos. El primero, los departamentos de los empleados de INFORMÁTICA y MARKETING (una fila por empleado):
SELECT dept_id FROM empleados WHERE dept_id IN (30, 50) ORDER BY 1;
DEPT_ID
----------
30
30
30
30
30
30
50
50
50
9 filas seleccionadas.
El segundo, el departamento del proyecto de cada asignación (una fila por asignación):
SELECT dept_id FROM asignaciones a JOIN proyectos p USING (proyecto_id) ORDER BY 1;
DEPT_ID
----------
30
30
30
30
30
30
30
50
50
50
50
60
60
60
14 filas seleccionadas.
Resumen de apariciones: el primero tiene 30 × 6 y 50 × 3; el segundo, 30 × 7, 50 × 4 y 60 × 3.
INTERSECT ALL devuelve cada valor tantas veces como el mínimo de sus apariciones en ambos lados (30: mín(6, 7) = 6; 50: mín(3, 4) = 3):
SELECT dept_id FROM empleados WHERE dept_id IN (30, 50)
INTERSECT ALL
SELECT p.dept_id FROM asignaciones a JOIN proyectos p ON p.proyecto_id = a.proyecto_id
ORDER BY 1;
DEPT_ID
----------
30
30
30
30
30
30
50
50
50
9 filas seleccionadas.
Frente a INTERSECT, que deja cada valor una vez:
SELECT dept_id FROM empleados WHERE dept_id IN (30, 50)
INTERSECT
SELECT p.dept_id FROM asignaciones a JOIN proyectos p ON p.proyecto_id = a.proyecto_id
ORDER BY 1;
DEPT_ID
----------
30
50
2 filas seleccionadas.
MINUS ALL resta apariciones: cada valor aparece (apariciones a la izquierda − apariciones a la derecha) veces, si es positivo. ¿Hay más asignaciones que empleados en algún departamento? Restamos a las asignaciones (30 × 7, 50 × 4, 60 × 3) los empleados de todos los departamentos (30 × 6, 50 × 3, 60 × 4…):
SELECT p.dept_id FROM asignaciones a JOIN proyectos p ON p.proyecto_id = a.proyecto_id
MINUS ALL
SELECT dept_id FROM empleados
ORDER BY 1;
DEPT_ID
----------
30
50
2 filas seleccionadas.
30: 7 − 6 = 1; 50: 4 − 3 = 1; 60: 3 − 4 < 0, no aparece. Es decir, en INFORMÁTICA y en MARKETING hay una asignación más que empleados (alguien está en dos proyectos o hay alguien de fuera). Y al revés, con EXCEPT ALL:
SELECT dept_id FROM empleados
EXCEPT ALL
SELECT p.dept_id FROM asignaciones a JOIN proyectos p ON p.proyecto_id = a.proyecto_id
ORDER BY 1;
DEPT_ID
----------
10
20
20
20
20
20
20
40
40
40
60
(nulo)
12 filas seleccionadas.
La última fila es el NULL de Tomás, que no se resta con nada.
6. Reglas de compatibilidad
Para combinar dos consultas con un operador de conjuntos, sus resultados deben ser compatibles para la unión (union-compatible), igual que en el álgebra relacional.
| Regla | Detalle |
|---|---|
| Mismo número de columnas | Si no, ORA-01789 |
| Tipos compatibles columna a columna (por posición) | Números con números, texto con texto, fechas con fechas; si no, ORA-01790 |
| Los nombres de las columnas del resultado son los de la primera consulta | Los alias de las demás se ignoran |
Un único ORDER BY al final | Se refiere a las columnas del resultado: por nombre (de la primera consulta), alias o posición |
Las consultas individuales no pueden llevar ORDER BY | Salvo dentro de una vista en línea o con FETCH FIRST entre paréntesis |
Los NULL se consideran iguales entre sí | Para eliminar duplicados, intersecar y restar |
| La comparación es por posición, no por nombre | SELECT a, b ∪ SELECT b, a mezcla columnas sin avisar si los tipos coinciden |
6.1. Número de columnas
SELECT emp_id, nombre FROM empleados
UNION
SELECT cliente_id FROM clientes;
SELECT emp_id, nombre FROM empleados
*
ERROR en línea 1:
ORA-01789: query block has incorrect number of result columns
Help: https://docs.oracle.com/error-help/db/ora-01789/
6.2. Tipos de datos
SELECT emp_id FROM empleados
UNION
SELECT nombre FROM clientes;
SELECT emp_id FROM empleados
*
ERROR en línea 1:
ORA-01790: expression must have same datatype as corresponding expression
Help: https://docs.oracle.com/error-help/db/ora-01790/
Oracle no convierte implícitamente entre familias de tipos en un operador de conjuntos. Si de verdad quieres mezclar, convierte tú explícitamente con TO_CHAR, TO_NUMBER, TO_DATE o CAST:
SELECT TO_CHAR(emp_id) AS codigo, nombre FROM empleados WHERE dept_id = 40
UNION ALL
SELECT nif, nombre FROM clientes WHERE provincia = 'Sevilla'
ORDER BY nombre;
CODIGO NOMBRE
---------- ---------------------------
23456789D Antonio Ruiz Pérez
B45678901 Clínica Dental Sonrisa
130 Isabel
131 Jorge
103 Pedro
5 filas seleccionadas.
Dentro de la misma familia sí hay conversión: VARCHAR2(30) con VARCHAR2(60) da VARCHAR2(60); NUMBER(6) con NUMBER(10,2) da NUMBER.
6.3. Nombres de columna y ORDER BY
Los nombres del resultado son los de la primera consulta:
SELECT nombre AS persona, salario AS importe FROM empleados WHERE dept_id = 50
UNION ALL
SELECT nombre, limite_credito FROM clientes WHERE ciudad = 'Madrid'
ORDER BY importe DESC;
PERSONA IMPORTE
---------------------- ----------
Academia Nova 8000
Laura 4300
Álvaro 2700
Nuria 2300
María José Herrera 2000
5 filas seleccionadas.
Un alias definido en la segunda consulta no existe para el ORDER BY:
SELECT nombre AS persona, salario AS importe FROM empleados WHERE dept_id = 50
UNION ALL
SELECT nombre, limite_credito AS limite FROM clientes WHERE ciudad = 'Madrid'
ORDER BY limite;
ORDER BY limite
*
ERROR en línea 4:
ORA-00904: "LIMITE": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/
Tampoco se puede ordenar por una expresión que no esté en la lista de columnas:
SELECT estado, COUNT(*) AS pedidos FROM pedidos GROUP BY estado
UNION ALL
SELECT 'TOTAL', COUNT(*) FROM pedidos
ORDER BY CASE WHEN estado = 'TOTAL' THEN 2 ELSE 1 END;
ORDER BY CASE WHEN estado = 'TOTAL' THEN 2 ELSE 1 END
*
ERROR en línea 4:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
Help: https://docs.oracle.com/error-help/db/ora-01785/
Si necesitas un criterio de orden especial, añádelo como columna del resultado, o envuelve la consulta compuesta en una vista en línea y ordena fuera (lo verás en el ejercicio 31).
6.4. ORDER BY en una rama
SELECT nombre FROM empleados WHERE dept_id = 40 ORDER BY nombre
UNION
SELECT nombre FROM clientes WHERE ciudad = 'Sevilla';
UNION
*
ERROR en línea 2:
ORA-03048: SQL reserved word 'UNION' is not syntactically valid following '...dept_id = 40 ORDER BY nombre
'
Help: https://docs.oracle.com/error-help/db/ora-03048/
Si una rama necesita ordenar (por ejemplo, para quedarse con sus N primeras filas), métela en una vista en línea:
SELECT * FROM (SELECT nombre, salario FROM empleados ORDER BY salario DESC FETCH FIRST 2 ROWS ONLY)
UNION ALL
SELECT * FROM (SELECT nombre, salario FROM empleados ORDER BY salario FETCH FIRST 2 ROWS ONLY);
NOMBRE SALARIO
--------------------------- ----------
Ana 6500
Marta 5000
Tomás 900
Rosa 1600
4 filas seleccionadas.
Los dos sueldos más altos y los dos más bajos en una sola consulta. Sin ORDER BY final, el orden del resultado no está garantizado (aquí coincide con el de las ramas).
6.5. Rellenar columnas que una rama no tiene
Cuando una consulta tiene columnas que la otra no, se completa con NULL (o con un valor por defecto):
SELECT nombre, salario, comision, NULL AS limite_credito
FROM empleados
WHERE dept_id = 20 AND comision > 0
UNION ALL
SELECT nombre, NULL, NULL, limite_credito
FROM clientes
WHERE comercial_id = 112
ORDER BY 1;
NOMBRE SALARIO COMISION LIMITE_CREDITO
--------------------------- ---------- ---------- --------------
Antonio Ruiz Pérez (nulo) (nulo) 1500
Carmen 2200 500 (nulo)
Clínica Dental Sonrisa (nulo) (nulo) 6000
David 2100 800 (nulo)
Elena 2300 300 (nulo)
Instituto Tecnológico Sur (nulo) (nulo) 20000
6 filas seleccionadas.
💡 Consejo: Un
NULLliteral no tiene tipo propio y Oracle lo adapta al de la otra rama. Si todas las ramas tienenNULLen una columna, o si la primera tieneNULLy quieres dejar claro el tipo, usaCAST(NULL AS NUMBER)oCAST(NULL AS VARCHAR2(30)).
7. Precedencia y paréntesis
⚠️ Importante: En Oracle todos los operadores de conjuntos tienen la misma precedencia y se evalúan de izquierda a derecha, salvo que se usen paréntesis. El estándar SQL da más prioridad a
INTERSECT(como la multiplicación frente a la suma), y la documentación de Oracle avisa de que una versión futura podría adoptar ese comportamiento. Por eso, usa siempre paréntesis cuando mezcles operadores distintos.
Tomemos tres conjuntos: A = departamentos 10, 20, 30; B = departamentos 60 y 70; C = departamentos con proyectos (10, 30, 50, 60).
SELECT dept_id FROM departamentos WHERE dept_id <= 30
UNION
SELECT dept_id FROM departamentos WHERE dept_id >= 60
INTERSECT
SELECT dept_id FROM proyectos
ORDER BY 1;
DEPT_ID
----------
10
30
60
3 filas seleccionadas.
Oracle 26ai ha calculado (A ∪ B) ∩ C = {10, 20, 30, 60, 70} ∩ {10, 30, 50, 60} = {10, 30, 60}: de izquierda a derecha. Con paréntesis explícitos el resultado es el mismo:
(SELECT dept_id FROM departamentos WHERE dept_id <= 30
UNION
SELECT dept_id FROM departamentos WHERE dept_id >= 60)
INTERSECT
SELECT dept_id FROM proyectos
ORDER BY 1;
DEPT_ID
----------
10
30
60
3 filas seleccionadas.
Pero si lo que querías era A ∪ (B ∩ C) (como se interpretaría con la precedencia del estándar), el resultado cambia:
SELECT dept_id FROM departamentos WHERE dept_id <= 30
UNION
(SELECT dept_id FROM departamentos WHERE dept_id >= 60
INTERSECT
SELECT dept_id FROM proyectos)
ORDER BY 1;
DEPT_ID
----------
10
20
30
60
4 filas seleccionadas.
{10, 20, 30} ∪ ({60, 70} ∩ {10, 30, 50, 60}) = {10, 20, 30} ∪ {60} = {10, 20, 30, 60}. El departamento 20 aparece solo en esta versión.
8. Usos prácticos de los operadores de conjuntos
8.1. Fila de total al final de un informe
Con UNION ALL se puede añadir una fila de totales a un listado agrupado (una alternativa manual a ROLLUP, módulo 29):
SELECT d.nombre AS departamento, SUM(e.salario) AS total
FROM empleados e
JOIN departamentos d ON d.dept_id = e.dept_id
GROUP BY d.nombre
UNION ALL
SELECT '** TOTAL **', SUM(salario)
FROM empleados
WHERE dept_id IS NOT NULL
ORDER BY 2;
DEPARTAMENTO TOTAL
-------------- ----------
DIRECCIÓN 6500
CONTABILIDAD 8450
LOGÍSTICA 8950
MARKETING 9300
VENTAS 15000
INFORMÁTICA 18800
** TOTAL ** 67000
7 filas seleccionadas.
Ordenar por el total pone la fila de total al final de forma natural, porque es la mayor.
8.2. Buscar diferencias entre dos tablas
Una tarea real muy frecuente: comparar una tabla con una copia (por ejemplo, la de producción con la de pruebas, o la de hoy con la de ayer). La diferencia simétrica (A − B) ∪ (B − A) devuelve las filas que difieren. Creamos una copia de productos y le hacemos tres cambios:
CREATE TABLE productos_copia AS SELECT * FROM productos;
UPDATE productos_copia SET precio = 949 WHERE producto_id = 1;
DELETE FROM productos_copia WHERE producto_id = 14;
INSERT INTO productos_copia (producto_id, nombre, categoria_id, precio, stock, descatalogado)
VALUES (21, 'Hub USB-C', 4, 39.90, 50, 'N');
COMMIT;
SELECT 'Solo en PRODUCTOS' AS donde, producto_id, nombre, precio FROM
(SELECT producto_id, nombre, precio FROM productos
MINUS
SELECT producto_id, nombre, precio FROM productos_copia)
UNION ALL
SELECT 'Solo en COPIA', producto_id, nombre, precio FROM
(SELECT producto_id, nombre, precio FROM productos_copia
MINUS
SELECT producto_id, nombre, precio FROM productos)
ORDER BY producto_id, donde;
DROP TABLE productos_copia PURGE;
Tabla creada.
1 fila actualizada.
1 fila suprimida.
1 fila creada.
Confirmación terminada.
DONDE PRODUCTO_ID NOMBRE PRECIO
----------------- ----------- --------------------------- ----------
Solo en COPIA 1 Portátil UltraBook 14 949
Solo en PRODUCTOS 1 Portátil UltraBook 14 899
Solo en PRODUCTOS 14 Altavoz Inteligente 49
Solo en COPIA 21 Hub USB-C 39,9
4 filas seleccionadas.
Tabla borrada.
Interpretación: el producto 1 está en ambas pero con distinto precio (fila modificada), el 14 solo está en el original (borrado en la copia) y el 21 solo en la copia (insertado).
💡 Consejo: Si las dos consultas devuelven exactamente las mismas filas,
(A MINUS B) UNION ALL (B MINUS A)devuelve cero filas. Es un truco excelente para comprobar que dos versiones de una consulta son equivalentes (por ejemplo, la versión con subconsulta y la versión conJOINde un mismo ejercicio). Ojo:MINUSelimina duplicados; si te importa cuántas veces aparece cada fila, usaMINUS ALL(21c+).
8.3. Operadores de conjuntos frente a otras técnicas
| Pregunta | Con operador de conjuntos | Alternativa |
|---|---|---|
| Filas de A que no están en B | A MINUS B | NOT EXISTS |
| Filas comunes a A y B | A INTERSECT B | EXISTS / IN / JOIN + DISTINCT |
| Juntar dos listados | UNION ALL | — (no hay alternativa sencilla) |
| Diferencias entre tablas | (A MINUS B) UNION ALL (B MINUS A) | FULL OUTER JOIN comparando columnas (más complejo con NULL) |
MINUS e INTERSECT solo pueden comparar las columnas que seleccionan. Si necesitas devolver más columnas que las comparadas, o la condición no es de igualdad, EXISTS / NOT EXISTS son más flexibles.
9. La cláusula WITH: expresiones de tabla comunes (CTE)
📘 Concepto: La cláusula
WITHpermite dar nombre a una subconsulta antes de la consulta principal y usarla después como si fuera una tabla. Cada subconsulta con nombre se llama CTE (Common Table Expression, expresión de tabla común); Oracle lo llama también subquery factoring (factorización de subconsultas). La CTE solo existe durante la ejecución de esa sentencia.
Sintaxis general:
WITH nombre_cte1 [(columna1, columna2, ...)] AS (
SELECT ...
),
nombre_cte2 AS (
SELECT ... FROM nombre_cte1 ... -- puede usar las CTE anteriores
)
SELECT ...
FROM nombre_cte2 ...;
Es como definir variables en matemáticas antes de usarlas: “sea I el importe de cada pedido; mostrar los pedidos cuyo I supera la media de I”.
9.1. Una CTE sencilla
En el módulo 31 (ejercicio 22) calculamos los pedidos por encima del importe medio repitiendo dos veces la fórmula del importe. Con WITH se escribe una sola vez:
WITH importes AS (
SELECT pedido_id,
ROUND(SUM(cantidad * precio_unitario * (1 - descuento / 100)), 2) AS importe
FROM lineas_pedido
GROUP BY pedido_id
)
SELECT pedido_id, importe
FROM importes
WHERE importe > (SELECT AVG(importe) FROM importes)
ORDER BY importe DESC;
PEDIDO_ID IMPORTE
---------- ----------
5010 11657,5
5016 8945,05
5002 7402,5
5004 6232,5
5009 4772,44
5 filas seleccionadas.
La CTE importes se usa dos veces: en el FROM principal y en la subconsulta del WHERE.
9.2. Varias CTE encadenadas
Cada CTE puede usar las que se han definido antes que ella. Esto permite construir una consulta compleja paso a paso, como una tubería. Ventas por comercial: primero el importe de cada pedido, luego las ventas de cada cliente y por último las de cada comercial.
WITH importes AS (
SELECT l.pedido_id,
SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)) AS importe
FROM lineas_pedido l
GROUP BY l.pedido_id
),
ventas_cliente AS (
SELECT p.cliente_id,
COUNT(*) AS num_pedidos,
ROUND(SUM(i.importe), 2) AS total
FROM pedidos p
JOIN importes i ON i.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
GROUP BY p.cliente_id
),
ventas_comercial AS (
SELECT c.comercial_id,
COUNT(*) AS num_clientes,
SUM(v.total) AS total
FROM clientes c
JOIN ventas_cliente v ON v.cliente_id = c.cliente_id
GROUP BY c.comercial_id
)
SELECT e.nombre || ' ' || e.apellidos AS empleado,
vc.num_clientes,
vc.total
FROM ventas_comercial vc
LEFT JOIN empleados e ON e.emp_id = vc.comercial_id
ORDER BY vc.total DESC;
EMPLEADO NUM_CLIENTES TOTAL
------------------------ ------------ ----------
Elena Navarro Castro 3 23833,55
Carmen Ruiz Ortega 3 22018,34
David Torres Romero 3 12931,97
Sergio Jiménez Vidal 2 2335
1 498,6
5 filas seleccionadas.
La última fila corresponde a los clientes sin comercial (comercial_id nulo): solo Laura Benítez (1009) tiene pedidos entre ellos. El LEFT JOIN la conserva con el nombre vacío (la concatenación NULL || ' ' || NULL da un espacio, no NULL, por eso no se muestra (nulo)).
Compara con la versión anidada equivalente: tres niveles de vistas en línea, una dentro de otra, que se leen de dentro hacia fuera. Con WITH se lee de arriba abajo, en el mismo orden en que se razona.
flowchart LR
LP[(lineas_pedido)] --> I[importes]
P[(pedidos)] --> VC[ventas_cliente]
I --> VC
C[(clientes)] --> VCO[ventas_comercial]
VC --> VCO
VCO --> F[consulta final]
E[(empleados)] --> F
9.3. Lista de columnas de la CTE
Los nombres de las columnas pueden darse en la cabecera de la CTE en lugar de con alias dentro:
WITH resumen (dept_id, num_emp, media) AS (
SELECT dept_id, COUNT(*), ROUND(AVG(salario), 2)
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
)
SELECT d.nombre AS departamento, r.num_emp, r.media
FROM resumen r
JOIN departamentos d ON d.dept_id = r.dept_id
WHERE r.media > (SELECT AVG(media) FROM resumen)
ORDER BY r.media DESC;
DEPARTAMENTO NUM_EMP MEDIA
-------------- ---------- ----------
DIRECCIÓN 1 6500
1 fila seleccionada.
(La media de las medias departamentales es 3381,25; solo DIRECCIÓN la supera.) La lista de columnas es obligatoria en las CTE recursivas (sección 10).
9.4. Reutilizar una CTE varias veces
Una CTE puede combinarse consigo misma. Evolución de las ventas de cada cliente entre 2025 y 2026:
WITH ventas_anio AS (
SELECT EXTRACT(YEAR FROM p.fecha_pedido) AS anio,
p.cliente_id,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS total
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
GROUP BY EXTRACT(YEAR FROM p.fecha_pedido), p.cliente_id
)
SELECT a.cliente_id, a.total AS total_2025, b.total AS total_2026,
b.total - a.total AS variacion
FROM ventas_anio a
JOIN ventas_anio b ON b.cliente_id = a.cliente_id
WHERE a.anio = 2025
AND b.anio = 2026
ORDER BY variacion DESC;
CLIENTE_ID TOTAL_2025 TOTAL_2026 VARIACION
---------- ---------- ---------- ----------
1005 936 1296 360
1009 79,8 418,8 339
1001 2242,1 2462,05 219,95
1012 1555 1499 -56
1007 2539,13 1663,64 -875,49
1010 11657,5 8945,05 -2712,45
1002 12174,94 2085,25 -10089,69
7 filas seleccionadas.
Solo aparecen los clientes con ventas en ambos años (un JOIN interno). El 1008 tiene pedidos en los dos años, pero el de 2025 está cancelado.
9.5. CTE y operadores de conjuntos
Las CTE combinan muy bien con los operadores de conjuntos, porque permiten definir una vez los conjuntos y luego operar con ellos:
WITH clientes_2025 AS (
SELECT DISTINCT cliente_id FROM pedidos
WHERE fecha_pedido >= DATE '2025-01-01' AND fecha_pedido < DATE '2026-01-01'
),
clientes_2026 AS (
SELECT DISTINCT cliente_id FROM pedidos
WHERE fecha_pedido >= DATE '2026-01-01' AND fecha_pedido < DATE '2027-01-01'
)
SELECT 'Solo 2025' AS grupo, COUNT(*) AS clientes FROM (SELECT * FROM clientes_2025 MINUS SELECT * FROM clientes_2026)
UNION ALL
SELECT 'Solo 2026', COUNT(*) FROM (SELECT * FROM clientes_2026 MINUS SELECT * FROM clientes_2025)
UNION ALL
SELECT 'Ambos años', COUNT(*) FROM (SELECT * FROM clientes_2025 INTERSECT SELECT * FROM clientes_2026);
GRUPO CLIENTES
----------- ----------
Solo 2025 3
Solo 2026 1
Ambos años 8
3 filas seleccionadas.
9.6. WITH en sentencias DML
Una CTE puede preceder a la consulta de un INSERT ... SELECT (la cláusula WITH va después de INSERT INTO tabla (...)), y también se usa en CREATE TABLE ... AS y CREATE VIEW ... AS:
CREATE TABLE resumen_demo (dept_id NUMBER(4), masa NUMBER(12,2));
INSERT INTO resumen_demo (dept_id, masa)
WITH masas AS (
SELECT dept_id, SUM(salario) AS masa
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
)
SELECT dept_id, masa FROM masas WHERE masa > 9000;
SELECT * FROM resumen_demo ORDER BY dept_id;
DROP TABLE resumen_demo PURGE;
Tabla creada.
3 filas creadas.
DEPT_ID MASA
---------- ----------
20 15000
30 18800
50 9300
3 filas seleccionadas.
Tabla borrada.
9.7. Reglas de las CTE
- Una CTE solo puede referirse a CTE definidas antes que ella (salvo a sí misma, si es recursiva):
WITH b AS (SELECT n * 2 AS doble FROM a),
a AS (SELECT 1 AS n FROM dual)
SELECT * FROM b;
WITH b AS (SELECT n * 2 AS doble FROM a),
*
ERROR en línea 1:
ORA-32031: illegal reference of a query name in WITH clause
Help: https://docs.oracle.com/error-help/db/ora-32031/
- Su ámbito es la sentencia en la que se define: no existe fuera de ella (para eso están las vistas, módulo 26).
- Una CTE definida y no usada no da error: simplemente se ignora.
WITH no_usada AS (SELECT * FROM empleados)
SELECT COUNT(*) AS num FROM departamentos;
NUM
----------
7
1 fila seleccionada.
- El optimizador decide si materializa la CTE (la calcula una vez y guarda el resultado en una tabla temporal) o la expande en línea como si fuera una subconsulta. Suele materializarla cuando se usa varias veces. Puede forzarse con las sugerencias (hints)
/*+ MATERIALIZE */e/*+ INLINE */, aunque rara vez hace falta.
9.8. ¿CTE o subconsulta?
| Criterio | Subconsulta anidada | CTE con WITH |
|---|---|---|
| Legibilidad | Se lee de dentro hacia fuera | Se lee de arriba abajo, por pasos con nombre |
| Reutilización | Hay que repetir el código | Se define una vez y se usa las veces que haga falta |
| Depuración | Difícil probar partes | Fácil: cambia el SELECT final por SELECT * FROM cte_intermedia |
| Recursividad | Imposible | CTE recursiva (sección 10) |
| Correlación | Puede ser correlacionada | No puede referirse a la consulta principal |
| Rendimiento | Igual en la mayoría de casos | Igual, o mejor si se materializa una CTE muy reutilizada |
💡 Consejo: Regla práctica: si una subconsulta se repite, o si la consulta tiene más de dos niveles de anidamiento, reescríbela con
WITH. Es la forma profesional de escribir SQL analítico.
10. CTE recursivas
📘 Concepto: Una CTE recursiva es una CTE que se referencia a sí misma. Consta de dos partes unidas por
UNION ALL:
- El miembro ancla (anchor member): una consulta que no usa la CTE y produce las filas iniciales.
- El miembro recursivo (recursive member): una consulta que combina la CTE con otras tablas para producir nuevas filas a partir de las obtenidas en el paso anterior.
Oracle ejecuta el ancla, luego el miembro recursivo sobre esas filas, luego otra vez sobre las nuevas filas obtenidas, y así hasta que el miembro recursivo no produzca ninguna fila. Disponible desde Oracle 11g Release 2 (estándar SQL:1999).
Es la herramienta para recorrer jerarquías (organigramas, árboles de categorías, listas de materiales), grafos (rutas) y para generar filas (series de números o de fechas).
flowchart TB
A["Miembro ancla<br/>(filas del nivel 1)"] --> R0["Resultado: nivel 1"]
R0 --> M1["Miembro recursivo<br/>sobre las filas nuevas"]
M1 --> R1["Añadir filas del nivel 2"]
R1 --> M2["Miembro recursivo<br/>sobre las filas nuevas"]
M2 --> R2["Añadir filas del nivel 3"]
R2 --> MX["... hasta que no salgan filas nuevas"]
10.1. El organigrama con nivel y ruta
WITH jerarquia (emp_id, nombre, jefe_id, nivel, ruta) AS (
-- Miembro ancla: la raíz del organigrama
SELECT emp_id, nombre, jefe_id, 1, CAST(nombre AS VARCHAR2(200))
FROM empleados
WHERE jefe_id IS NULL
UNION ALL
-- Miembro recursivo: los subordinados directos de las filas ya obtenidas
SELECT e.emp_id, e.nombre, e.jefe_id, j.nivel + 1, j.ruta || ' > ' || e.nombre
FROM empleados e
JOIN jerarquia j ON e.jefe_id = j.emp_id
)
SELECT emp_id, nivel, ruta
FROM jerarquia
ORDER BY ruta;
EMP_ID NIVEL RUTA
---------- ---------- --------------------------------------------------
100 1 Ana
105 2 Ana > Javier
152 3 Ana > Javier > Cristina
151 3 Ana > Javier > Manuel
150 3 Ana > Javier > Rosa
104 2 Ana > Laura
141 3 Ana > Laura > Álvaro
140 3 Ana > Laura > Nuria
...
24 filas seleccionadas.
Paso a paso:
| Iteración | Qué hace | Filas nuevas |
|---|---|---|
| Ancla | Empleados con jefe_id IS NULL | Ana (nivel 1) |
| 1.ª recursión | Empleados cuyo jefe es Ana | Luis, Marta, Pedro, Laura, Javier (nivel 2) |
| 2.ª recursión | Empleados cuyo jefe es alguno de los cinco anteriores | 15 empleados (nivel 3) |
| 3.ª recursión | Empleados cuyo jefe es alguno de esos 15 | Raquel, Iván, Sara (nivel 4), subordinados de Pablo |
| 4.ª recursión | Empleados cuyo jefe es Raquel, Iván o Sara | Ninguno: fin |
Detalles importantes:
- La lista de columnas es obligatoria en una CTE recursiva.
- El miembro ancla y el recursivo deben unirse con
UNION ALL(noUNION). - La columna
niveles un contador: el ancla pone 1 y el recursivo suma 1 al nivel del padre. - La columna
rutaacumula los nombres. ElCAST(... AS VARCHAR2(200))en el ancla fija un tipo con espacio suficiente: los tipos de las columnas de la CTE se deducen del miembro ancla ynombrees soloVARCHAR2(30). En Oracle 26ai la consulta funciona también sin elCAST, pero declararlo es una buena práctica que evita errores de longitud y deja claro el tipo. - Ordenar por
rutaes un truco para obtener un orden de árbol, pero es frágil (depende de los nombres). La forma correcta esSEARCH(10.3).
Mira los errores que se producen si olvidas la lista de columnas o usas UNION:
WITH numeros AS (
SELECT 1 AS n FROM dual
UNION ALL
SELECT n + 1 FROM numeros WHERE n < 5
)
SELECT n FROM numeros;
WITH numeros AS (
*
ERROR en línea 1:
ORA-32039: missing column alias list in recursive WITH clause element NUMEROS
Help: https://docs.oracle.com/error-help/db/ora-32039/
WITH numeros (n) AS (
SELECT 1 FROM dual
UNION
SELECT n + 1 FROM numeros WHERE n < 5
)
SELECT n FROM numeros;
WITH numeros (n) AS (
*
ERROR en línea 1:
ORA-32040: missing UNION ALL in recursive WITH clause element NUMEROS
Help: https://docs.oracle.com/error-help/db/ora-32040/
10.2. Un subárbol y la cadena de mando
Cambiando el ancla se recorre solo una rama. El equipo completo de Marta Sánchez (102), directo e indirecto:
WITH equipo (emp_id, nombre, oficio, nivel) AS (
SELECT emp_id, nombre, oficio, 0
FROM empleados
WHERE emp_id = 102
UNION ALL
SELECT e.emp_id, e.nombre, e.oficio, q.nivel + 1
FROM empleados e
JOIN equipo q ON e.jefe_id = q.emp_id
)
SELECT emp_id, nombre, oficio, nivel
FROM equipo
ORDER BY nivel, emp_id;
EMP_ID NOMBRE OFICIO NIVEL
---------- --------------------------- ----------------- ----------
102 Marta DIRECTOR 0
120 Pablo ANALISTA 1
124 Hugo TÉCNICO SISTEMAS 1
160 Tomás BECARIO 1
121 Raquel PROGRAMADOR 2
122 Iván PROGRAMADOR 2
123 Sara PROGRAMADOR 2
7 filas seleccionadas.
Y cambiando el sentido de la condición de combinación se sube por la jerarquía. La cadena de mando de Sara Ortiz (123) hasta la dirección general:
WITH cadena (emp_id, nombre, oficio, jefe_id, paso) AS (
SELECT emp_id, nombre, oficio, jefe_id, 0
FROM empleados
WHERE emp_id = 123
UNION ALL
SELECT j.emp_id, j.nombre, j.oficio, j.jefe_id, c.paso + 1
FROM empleados j
JOIN cadena c ON j.emp_id = c.jefe_id
)
SELECT paso, emp_id, nombre, oficio
FROM cadena
ORDER BY paso;
PASO EMP_ID NOMBRE OFICIO
---------- ---------- --------------------------- -----------------
0 123 Sara PROGRAMADOR
1 120 Pablo ANALISTA
2 102 Marta DIRECTOR
3 100 Ana DIRECTOR GENERAL
4 filas seleccionadas.
| Dirección | Condición en el miembro recursivo | Significado |
|---|---|---|
| Hacia abajo (subordinados) | e.jefe_id = cte.emp_id | “busca a quienes tienen por jefe a las filas actuales” |
| Hacia arriba (jefes) | e.emp_id = cte.jefe_id | “busca al jefe de las filas actuales” |
10.3. Orden del resultado: SEARCH DEPTH FIRST y BREADTH FIRST
Sin ORDER BY, el orden en que salen las filas de una CTE recursiva no está garantizado (en la práctica suele salir por niveles). La cláusula SEARCH, que va justo después del paréntesis de cierre de la CTE, genera una columna de orden:
SEARCH DEPTH FIRST BY col SET orden: recorrido en profundidad (cada nodo seguido de todos sus descendientes, como un índice de libro o un explorador de archivos). Los hermanos se ordenan porcol.SEARCH BREADTH FIRST BY col SET orden: recorrido en anchura (primero todo el nivel 1, luego todo el nivel 2…). Dentro de cada nivel se ordena porcol.
Organigrama con sangría en profundidad, hermanos en orden alfabético:
WITH jerarquia (emp_id, nombre, oficio, jefe_id, nivel) AS (
SELECT emp_id, nombre, oficio, jefe_id, 1
FROM empleados
WHERE jefe_id IS NULL
UNION ALL
SELECT e.emp_id, e.nombre, e.oficio, e.jefe_id, j.nivel + 1
FROM empleados e
JOIN jerarquia j ON e.jefe_id = j.emp_id
)
SEARCH DEPTH FIRST BY nombre SET orden
SELECT LPAD(' ', 2 * (nivel - 1)) || nombre AS arbol, oficio, nivel
FROM jerarquia
ORDER BY orden;
ARBOL OFICIO NIVEL
------------------------------ ----------------- ----------
Ana DIRECTOR GENERAL 1
Javier DIRECTOR 2
Cristina ADMINISTRATIVO 3
Manuel OPERARIO 3
Rosa OPERARIO 3
Laura DIRECTOR 2
Álvaro ANALISTA 3
Nuria DISEÑADOR 3
Luis DIRECTOR 2
Carmen VENDEDOR 3
David VENDEDOR 3
Elena VENDEDOR 3
Lucía ADMINISTRATIVO 3
Sergio VENDEDOR 3
Marta DIRECTOR 2
Hugo TÉCNICO SISTEMAS 3
Pablo ANALISTA 3
Iván PROGRAMADOR 4
Raquel PROGRAMADOR 4
Sara PROGRAMADOR 4
Tomás BECARIO 3
Pedro DIRECTOR 2
Isabel CONTABLE 3
Jorge ADMINISTRATIVO 3
24 filas seleccionadas.
LPAD(' ', 2 * (nivel - 1)) genera 0, 2, 4 o 6 espacios según el nivel, lo que produce la sangría visual del árbol.
El mismo recorrido en anchura, por código de empleado (primeras 12 filas):
WITH jerarquia (emp_id, nombre, jefe_id, nivel) AS (
SELECT emp_id, nombre, jefe_id, 1
FROM empleados
WHERE jefe_id IS NULL
UNION ALL
SELECT e.emp_id, e.nombre, e.jefe_id, j.nivel + 1
FROM empleados e
JOIN jerarquia j ON e.jefe_id = j.emp_id
)
SEARCH BREADTH FIRST BY emp_id SET orden
SELECT orden, nivel, emp_id, nombre, jefe_id
FROM jerarquia
ORDER BY orden
FETCH FIRST 12 ROWS ONLY;
ORDEN NIVEL EMP_ID NOMBRE JEFE_ID
---------- ---------- ---------- --------------------------- ----------
1 1 100 Ana (nulo)
2 2 101 Luis 100
3 2 102 Marta 100
4 2 103 Pedro 100
5 2 104 Laura 100
6 2 105 Javier 100
7 3 110 Carmen 101
8 3 111 David 101
9 3 112 Elena 101
10 3 113 Sergio 101
11 3 114 Lucía 101
12 3 120 Pablo 102
12 filas seleccionadas.
10.4. El árbol de categorías
WITH arbol_cat (categoria_id, nombre, padre_id, nivel, ruta) AS (
SELECT categoria_id, nombre, categoria_padre_id, 1, CAST(nombre AS VARCHAR2(200))
FROM categorias
WHERE categoria_padre_id IS NULL
UNION ALL
SELECT c.categoria_id, c.nombre, c.categoria_padre_id, a.nivel + 1,
a.ruta || ' / ' || c.nombre
FROM categorias c
JOIN arbol_cat a ON c.categoria_padre_id = a.categoria_id
)
SEARCH DEPTH FIRST BY nombre SET orden
SELECT LPAD(' ', 4 * (nivel - 1)) || nombre AS arbol, nivel, ruta
FROM arbol_cat
ORDER BY orden;
ARBOL NIVEL RUTA
------------------------------ ---------- --------------------------------------------------
Electrónica 1 Electrónica
Audio 2 Electrónica / Audio
Telefonía 2 Electrónica / Telefonía
Informática 1 Informática
Ordenadores sobremesa 2 Informática / Ordenadores sobremesa
Periféricos 2 Informática / Periféricos
Monitores 3 Informática / Periféricos / Monitores
Ratones y teclados 3 Informática / Periféricos / Ratones y teclados
Portátiles 2 Informática / Portátiles
Oficina 1 Oficina
Mobiliario 2 Oficina / Mobiliario
11 filas seleccionadas.
En el módulo 31 calculamos la categoría raíz de cada categoría con dos autocombinaciones, lo que solo funcionaba para tres niveles. Con una CTE recursiva que “arrastra” la raíz desde el ancla funciona para cualquier profundidad. Productos y stock total por categoría raíz:
WITH raices (categoria_id, raiz_id, raiz) AS (
SELECT categoria_id, categoria_id, nombre
FROM categorias
WHERE categoria_padre_id IS NULL
UNION ALL
SELECT c.categoria_id, r.raiz_id, r.raiz
FROM categorias c
JOIN raices r ON c.categoria_padre_id = r.categoria_id
)
SELECT r.raiz AS categoria, COUNT(p.producto_id) AS productos, SUM(p.stock) AS stock_total
FROM raices r
LEFT JOIN productos p ON p.categoria_id = r.categoria_id
GROUP BY r.raiz
ORDER BY r.raiz;
CATEGORIA PRODUCTOS STOCK_TOTAL
---------------------- ---------- -----------
Electrónica 5 152
Informática 13 510
Oficina 2 26
3 filas seleccionadas.
10.5. Agregar sobre subárboles
Si el ancla parte de todos los empleados y cada uno arrastra su propio identificador como “raíz”, la CTE genera, para cada empleado, la lista de todos los que están por debajo de él (incluido él mismo). Agrupando por la raíz se obtienen totales por subárbol. Número de subordinados (directos e indirectos) y masa salarial de cada equipo:
WITH bajo_jefe (raiz_id, emp_id, salario) AS (
SELECT emp_id, emp_id, salario
FROM empleados
UNION ALL
SELECT b.raiz_id, e.emp_id, e.salario
FROM empleados e
JOIN bajo_jefe b ON e.jefe_id = b.emp_id
)
SELECT j.emp_id, j.nombre, j.oficio,
COUNT(*) - 1 AS subordinados,
SUM(b.salario) AS masa_subarbol
FROM bajo_jefe b
JOIN empleados j ON j.emp_id = b.raiz_id
GROUP BY j.emp_id, j.nombre, j.oficio
HAVING COUNT(*) > 1
ORDER BY masa_subarbol DESC;
EMP_ID NOMBRE OFICIO SUBORDINADOS MASA_SUBARBOL
---------- --------------------------- ----------------- ------------ -------------
100 Ana DIRECTOR GENERAL 23 67900
102 Marta DIRECTOR 6 19700
101 Luis DIRECTOR 5 15000
120 Pablo ANALISTA 3 11300
104 Laura DIRECTOR 2 9300
105 Javier DIRECTOR 3 8950
103 Pedro DIRECTOR 2 8450
7 filas seleccionadas.
Ana tiene por debajo a los otros 23 empleados y su “subárbol” suma toda la masa salarial (67 900 €). El equipo de Marta incluye a Tomás, que no tiene departamento pero sí jefe: por eso 19 700 € es más que los 18 800 € del departamento de INFORMÁTICA.
10.6. Generación de series
Una CTE recursiva no necesita tablas: puede generar filas. Los números del 1 al 10:
WITH numeros (n) AS (
SELECT 1 FROM dual
UNION ALL
SELECT n + 1 FROM numeros WHERE n < 10
)
SELECT n FROM numeros;
N
----------
1
2
3
4
5
6
7
8
9
10
10 filas seleccionadas.
La condición WHERE n < 10 del miembro recursivo es la condición de parada: cuando ninguna fila la cumple, el recursivo no produce filas y la recursión termina. Del 1 al 100 (comprobamos con agregados en vez de listar cien filas):
WITH numeros (n) AS (
SELECT 1 FROM dual
UNION ALL
SELECT n + 1 FROM numeros WHERE n < 100
)
SELECT COUNT(*) AS cuantos, SUM(n) AS suma, MIN(n) AS minimo, MAX(n) AS maximo
FROM numeros;
CUANTOS SUMA MINIMO MAXIMO
---------- ---------- ---------- ----------
100 5050 1 100
1 fila seleccionada.
(5050 = 100 · 101 / 2, la famosa suma de Gauss.)
Lo más útil es generar fechas para que aparezcan también los días o meses sin datos. Un GROUP BY sobre pedidos solo muestra los días en los que hubo pedidos; combinando un calendario generado con un LEFT JOIN aparecen todos. Pedidos por día del 1 al 12 de julio de 2026:
WITH dias (dia) AS (
SELECT DATE '2026-07-01' FROM dual
UNION ALL
SELECT dia + 1 FROM dias WHERE dia < DATE '2026-07-12'
)
SELECT TO_CHAR(d.dia, 'DD/MM/YYYY') AS dia,
TO_CHAR(d.dia, 'Dy', 'NLS_DATE_LANGUAGE=SPANISH') AS dsem,
COUNT(p.pedido_id) AS pedidos
FROM dias d
LEFT JOIN pedidos p ON p.fecha_pedido = d.dia
GROUP BY d.dia
ORDER BY d.dia;
DIA DSEM PEDIDOS
---------- ------------ ----------
01/07/2026 Mié 1
02/07/2026 Jue 0
03/07/2026 Vie 0
04/07/2026 Sáb 0
05/07/2026 Dom 0
06/07/2026 Lun 0
07/07/2026 Mar 0
08/07/2026 Mié 1
09/07/2026 Jue 0
10/07/2026 Vie 1
11/07/2026 Sáb 0
12/07/2026 Dom 0
12 filas seleccionadas.
COUNT(p.pedido_id) cuenta 0 en los días sin pedidos porque solo cuenta valores no nulos (COUNT(*) contaría 1). Ventas de cada mes de 2025, incluidos los meses sin ventas:
WITH meses (mes) AS (
SELECT DATE '2025-01-01' FROM dual
UNION ALL
SELECT ADD_MONTHS(mes, 1) FROM meses WHERE mes < DATE '2025-12-01'
),
ventas AS (
SELECT TRUNC(p.fecha_pedido, 'MM') AS mes,
SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)) AS importe
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
GROUP BY TRUNC(p.fecha_pedido, 'MM')
)
SELECT TO_CHAR(m.mes, 'YYYY-MM') AS mes,
NVL(ROUND(v.importe, 2), 0) AS ventas
FROM meses m
LEFT JOIN ventas v ON v.mes = m.mes
ORDER BY m.mes;
MES VENTAS
------- ----------
2025-01 9205,4
2025-02 6791,4
2025-03 1375,2
2025-04 2539,13
2025-05 4772,44
2025-06 12656,5
2025-07 0
2025-08 0
2025-09 1658,8
2025-10 1555
2025-11 358,8
2025-12 0
12 filas seleccionadas.
Julio, agosto y diciembre de 2025 aparecen con 0, cosa imposible con un simple GROUP BY sobre pedidos. Fíjate en que la consulta mezcla una CTE recursiva (meses) y una normal (ventas) en el mismo WITH.
💡 Consejo: Otra forma clásica en Oracle de generar series es
SELECT LEVEL AS n FROM dual CONNECT BY LEVEL <= 100, que verás en el módulo 33. La CTE recursiva es estándar y funciona igual en PostgreSQL, SQL Server, MySQL 8 o SQLite (en algunos conWITH RECURSIVE).
10.7. Ciclos: la cláusula CYCLE
En un grafo (por ejemplo, una red de rutas) puede haber ciclos: A → B → C → A. Una CTE recursiva que lo recorra sin protección daría vueltas indefinidamente. Creamos una pequeña tabla de rutas con un ciclo Madrid → Valencia → Sevilla → Madrid:
CREATE TABLE rutas_demo (
origen VARCHAR2(10),
destino VARCHAR2(10)
);
INSERT INTO rutas_demo VALUES ('Madrid', 'Valencia');
INSERT INTO rutas_demo VALUES ('Valencia', 'Sevilla');
INSERT INTO rutas_demo VALUES ('Sevilla', 'Madrid');
INSERT INTO rutas_demo VALUES ('Sevilla', 'Bilbao');
COMMIT;
Todos los viajes posibles desde Madrid:
WITH viaje (destino, camino, saltos) AS (
SELECT destino, CAST('Madrid > ' || destino AS VARCHAR2(200)), 1
FROM rutas_demo
WHERE origen = 'Madrid'
UNION ALL
SELECT r.destino, v.camino || ' > ' || r.destino, v.saltos + 1
FROM rutas_demo r
JOIN viaje v ON r.origen = v.destino
)
SELECT destino, camino, saltos FROM viaje;
ERROR:
ORA-32044: cycle detected while executing recursive WITH query
Help: https://docs.oracle.com/error-help/db/ora-32044/
ninguna fila seleccionada
Oracle detecta el bucle y aborta la consulta. La cláusula CYCLE permite manejarlo: indica qué columnas identifican un nodo y crea una columna marcadora que vale un valor cuando una fila repite un nodo ya visitado en su propia rama (sus antepasados en la recursión). Esa fila se devuelve marcada, pero no se sigue expandiendo:
WITH viaje (destino, camino, saltos) AS (
SELECT destino, CAST('Madrid > ' || destino AS VARCHAR2(200)), 1
FROM rutas_demo
WHERE origen = 'Madrid'
UNION ALL
SELECT r.destino, v.camino || ' > ' || r.destino, v.saltos + 1
FROM rutas_demo r
JOIN viaje v ON r.origen = v.destino
)
CYCLE destino SET es_ciclo TO 'S' DEFAULT 'N'
SELECT destino, camino, saltos, es_ciclo FROM viaje;
DESTINO CAMINO SALTOS ES_CICLO
---------- ------------------------------------------------------- ---------- --------
Valencia Madrid > Valencia 1 N
Sevilla Madrid > Valencia > Sevilla 2 N
Madrid Madrid > Valencia > Sevilla > Madrid 3 N
Bilbao Madrid > Valencia > Sevilla > Bilbao 3 N
Valencia Madrid > Valencia > Sevilla > Madrid > Valencia 4 S
5 filas seleccionadas.
Observa un detalle sutil: la fila “Madrid” del salto 3 no se marca como ciclo, porque “Madrid” nunca había sido el destino de una fila anterior de esa rama (el origen del ancla no es una fila de la CTE). El ciclo se detecta en el salto 4, al volver a Valencia. Normalmente se filtran las filas marcadas con WHERE es_ciclo = 'N'. La tabla rutas_demo se queda creada: la usarás en el ejercicio 28, cuya solución la borra al final.
Sintaxis: CYCLE columnas SET columna_marca TO 'valor_ciclo' DEFAULT 'valor_normal'. Lo habitual (y lo que funciona en todas las versiones) es usar literales de un carácter, como 'S' y 'N'.
⚠️ Importante: El organigrama de EMPRESA no tiene ciclos (la clave ajena
jefe_idno los impide, pero los datos son correctos). En datos reales, un error de carga (A es jefe de B y B de A) haría fallar cualquier consulta recursiva sinCYCLEconORA-32044. Para jerarquías críticas, añadirCYCLEes una protección barata.
10.8. CTE recursiva frente a CONNECT BY
Oracle tiene, desde mucho antes de que existieran las CTE recursivas, su propia sintaxis para consultas jerárquicas: CONNECT BY, que se estudia en profundidad en el módulo 33. Como adelanto, el equipo de Marta con CONNECT BY:
SELECT LPAD(' ', 2 * (LEVEL - 1)) || nombre AS arbol, LEVEL AS nivel
FROM empleados
START WITH emp_id = 102
CONNECT BY PRIOR emp_id = jefe_id
ORDER SIBLINGS BY nombre;
ARBOL NIVEL
------------------------------ ----------
Marta 1
Hugo 2
Pablo 2
Iván 3
Raquel 3
Sara 3
Tomás 2
7 filas seleccionadas.
| Aspecto | CTE recursiva (WITH) | CONNECT BY |
|---|---|---|
| Estándar | Sí (SQL:1999), portable | No, exclusiva de Oracle |
| Desde | Oracle 11g R2 | Oracle 2 (años 80) |
| Concisión | Más larga | Muy concisa |
| Nivel | Hay que calcularlo (nivel + 1) | Pseudocolumna LEVEL |
| Ruta | Concatenación manual | SYS_CONNECT_BY_PATH |
| Raíz, hoja | Arrastrar columnas / subconsulta | CONNECT_BY_ROOT, CONNECT_BY_ISLEAF |
| Orden de árbol | SEARCH DEPTH FIRST | ORDER SIBLINGS BY |
| Ciclos | CYCLE | NOCYCLE y CONNECT_BY_ISCYCLE |
| Cálculos acumulados por el camino (multiplicar cantidades, sumar distancias) | Natural | Difícil |
| Generar series | Sí | Sí (CONNECT BY LEVEL <= n) |
11. El constructor de valores de tabla VALUES
⚠️ Importante: Disponible desde Oracle 23ai. En Oracle 19c/21c usa
SELECT ... FROM dual UNION ALL SELECT ... FROM dual.
Oracle 23ai incorpora el constructor de valores de tabla (table value constructor) del estándar: VALUES (fila1), (fila2), ... produce una tabla a partir de literales. Se usa en el FROM con un alias para la tabla y sus columnas:
SELECT *
FROM (VALUES (20, 16000),
(30, 15000),
(50, 10000)) objetivos (dept_id, objetivo);
DEPT_ID OBJETIVO
---------- ----------
20 16000
30 15000
50 10000
3 filas seleccionadas.
También puede ser el cuerpo de una CTE, lo que resulta muy cómodo para “tablas de parámetros” que solo necesitas en una consulta. Masa salarial frente a un objetivo de gasto por departamento:
WITH objetivos (dept_id, objetivo) AS (
VALUES (20, 16000), (30, 15000), (50, 10000)
)
SELECT d.nombre AS departamento,
o.objetivo,
SUM(e.salario) AS masa_salarial,
SUM(e.salario) - o.objetivo AS desviacion
FROM objetivos o
JOIN departamentos d ON d.dept_id = o.dept_id
JOIN empleados e ON e.dept_id = o.dept_id
GROUP BY d.nombre, o.objetivo
ORDER BY desviacion DESC;
DEPARTAMENTO OBJETIVO MASA_SALARIAL DESVIACION
-------------- ---------- ------------- ----------
INFORMÁTICA 15000 18800 3800
MARKETING 10000 9300 -700
VENTAS 16000 15000 -1000
3 filas seleccionadas.
La alternativa clásica, válida en cualquier versión:
WITH objetivos (dept_id, objetivo) AS (
SELECT 20, 16000 FROM dual UNION ALL
SELECT 30, 15000 FROM dual UNION ALL
SELECT 50, 10000 FROM dual
)
SELECT dept_id, objetivo FROM objetivos ORDER BY dept_id;
DEPT_ID OBJETIVO
---------- ----------
20 16000
30 15000
50 10000
3 filas seleccionadas.
12. Errores frecuentes y buenas prácticas
| Error | Causa | Solución |
|---|---|---|
ORA-01789 | Distinto número de columnas en las ramas | Igualar columnas, rellenar con NULL |
ORA-01790 | Tipos incompatibles en una misma posición | Convertir con TO_CHAR, CAST… |
ORA-00904 en el ORDER BY | Se usa un alias de la segunda rama | Usar nombres de la primera rama o posiciones |
ORA-01785 | ORDER BY por una expresión en una consulta compuesta | Añadir la expresión como columna o envolver en vista en línea |
ORDER BY en una rama (ORA-03048/ORA-00933) | Solo se admite uno al final | Vista en línea si una rama necesita ordenar |
| Resultados inesperados con 3+ operadores | Precedencia de izquierda a derecha | Paréntesis siempre |
UNION lento | Elimina duplicados innecesariamente | UNION ALL si no puede haber duplicados |
Comparar por columnas no clave con MINUS | Filas distintas con mismos valores se confunden | Comparar por la clave |
ORA-32031 | Una CTE usa otra definida después | Reordenar las CTE |
ORA-32039 | CTE recursiva sin lista de columnas | WITH cte (col1, col2) AS (...) |
ORA-32040 | Ancla y recursivo unidos con UNION | UNION ALL |
ORA-32044 | Ciclo en los datos | Cláusula CYCLE |
| Recursión que no termina | Falta la condición de parada | WHERE n < límite en el miembro recursivo |
| Valores truncados o error de longitud en la ruta | El ancla fija un tipo corto | CAST(... AS VARCHAR2(n)) en el ancla |
13. Ejercicios
Todos los ejercicios usan el esquema EMPRESA (empresa_crear.sql, empresa_datos.sql).
Ejercicios resueltos
Ejercicio 1: Agenda de Valencia
Muestra en un único listado los empleados que trabajan en un departamento con sede en Valencia y los clientes cuya ciudad es Valencia, indicando el tipo de cada contacto. Ordena por tipo y nombre.
Ver solución
SELECT 'EMPLEADO' AS tipo, e.nombre || ' ' || e.apellidos AS contacto
FROM empleados e
JOIN departamentos d ON d.dept_id = e.dept_id
WHERE d.localidad = 'Valencia'
UNION ALL
SELECT 'CLIENTE', c.nombre
FROM clientes c
WHERE c.ciudad = 'Valencia'
ORDER BY tipo, contacto;
TIPO CONTACTO
--------- ---------------------------
CLIENTE Carlos Ibáñez Rey
CLIENTE Talleres Martín S.L.
EMPLEADO Hugo Rubio Marín
EMPLEADO Iván Castro Méndez
EMPLEADO Marta Sánchez Gil
EMPLEADO Pablo Moreno Herrera
EMPLEADO Raquel Molina Prieto
EMPLEADO Sara Ortiz Delgado
8 filas seleccionadas.
UNION ALL es correcto porque la columna tipo impide duplicados entre ramas.
Ejercicio 2: Productos vendidos ambos años
Lista los códigos de los productos que se han vendido tanto en 2025 como en 2026.
Ver solución
SELECT l.producto_id
FROM lineas_pedido l JOIN pedidos p ON p.pedido_id = l.pedido_id
WHERE p.fecha_pedido < DATE '2026-01-01'
INTERSECT
SELECT l.producto_id
FROM lineas_pedido l JOIN pedidos p ON p.pedido_id = l.pedido_id
WHERE p.fecha_pedido >= DATE '2026-01-01'
ORDER BY 1;
PRODUCTO_ID
-----------
1
2
3
4
5
7
9
10
...
15 filas seleccionadas.
Todos los pedidos son de 2025 o 2026, por eso basta con comparar con el 1 de enero de 2026.
Ejercicio 3: Productos que dejaron de venderse
Muestra código y nombre de los productos vendidos en 2025 que no se han vuelto a vender en 2026.
Ver solución
SELECT pr.producto_id, pr.nombre AS producto
FROM productos pr
WHERE pr.producto_id IN (SELECT l.producto_id
FROM lineas_pedido l JOIN pedidos p ON p.pedido_id = l.pedido_id
WHERE p.fecha_pedido < DATE '2026-01-01'
MINUS
SELECT l.producto_id
FROM lineas_pedido l JOIN pedidos p ON p.pedido_id = l.pedido_id
WHERE p.fecha_pedido >= DATE '2026-01-01')
ORDER BY pr.producto_id;
PRODUCTO_ID PRODUCTO
----------- ----------------------
6 Ratón inalámbrico
8 Pack teclado y ratón
17 Webcam HD
20 Tablet 10
4 filas seleccionadas.
El MINUS trabaja con códigos (la clave) y la consulta externa añade el nombre.
Ejercicio 4: Jefes con proyecto
Lista los códigos de los empleados que son jefes de alguien y además están asignados a algún proyecto.
Ver solución
SELECT jefe_id AS emp_id FROM empleados
INTERSECT
SELECT emp_id FROM asignaciones
ORDER BY 1;
EMP_ID
----------
104
105
120
3 filas seleccionadas.
Laura, Javier y Pablo. El NULL de jefe_id no aparece porque no hay ningún emp_id nulo en asignaciones.
Ejercicio 5: Asignados que no son de INFORMÁTICA
Lista los códigos de los empleados asignados a algún proyecto que no pertenecen al departamento 30.
Ver solución
SELECT emp_id FROM asignaciones
MINUS
SELECT emp_id FROM empleados WHERE dept_id = 30
ORDER BY 1;
EMP_ID
----------
104
105
110
140
141
150
152
7 filas seleccionadas.
Ejercicio 6: Clientes sin pedidos con MINUS
Muestra código y nombre de los clientes que no tienen pedidos, usando MINUS.
Ver solución
SELECT cliente_id, nombre AS cliente
FROM clientes
WHERE cliente_id IN (SELECT cliente_id FROM clientes
MINUS
SELECT cliente_id FROM pedidos);
CLIENTE_ID CLIENTE
---------- ---------------------------
1013 Carlos Ibáñez Rey
1014 Farmacia Central
2 filas seleccionadas.
Ejercicio 7: Diferencia simétrica de clientes
Lista los clientes que solo compraron en uno de los dos años (2025 o 2026), indicando en cuál.
Ver solución
WITH c25 AS (SELECT cliente_id FROM pedidos
WHERE fecha_pedido >= DATE '2025-01-01' AND fecha_pedido < DATE '2026-01-01'),
c26 AS (SELECT cliente_id FROM pedidos
WHERE fecha_pedido >= DATE '2026-01-01' AND fecha_pedido < DATE '2027-01-01')
(SELECT cliente_id, 'Solo 2025' AS cuando FROM (SELECT cliente_id FROM c25 MINUS SELECT cliente_id FROM c26))
UNION ALL
(SELECT cliente_id, 'Solo 2026' FROM (SELECT cliente_id FROM c26 MINUS SELECT cliente_id FROM c25))
ORDER BY 1;
CLIENTE_ID CUANDO
---------- ---------
1003 Solo 2025
1004 Solo 2025
1006 Solo 2025
1011 Solo 2026
4 filas seleccionadas.
(A − B) ∪ (B − A). Las dos ramas son disjuntas, así que UNION ALL es seguro.
Ejercicio 8: Comparar una tabla con su copia
Crea una copia de productos, modifica en ella el precio del producto 1, borra el 14 e inserta un producto nuevo. Escribe la consulta que muestre todas las diferencias entre ambas tablas y borra la copia.
Ver solución
Es exactamente el ejemplo de la sección 8.2:
CREATE TABLE productos_copia AS SELECT * FROM productos;
UPDATE productos_copia SET precio = 949 WHERE producto_id = 1;
DELETE FROM productos_copia WHERE producto_id = 14;
INSERT INTO productos_copia (producto_id, nombre, categoria_id, precio, stock, descatalogado)
VALUES (21, 'Hub USB-C', 4, 39.90, 50, 'N');
COMMIT;
SELECT 'Solo en PRODUCTOS' AS donde, producto_id, nombre, precio FROM
(SELECT producto_id, nombre, precio FROM productos
MINUS
SELECT producto_id, nombre, precio FROM productos_copia)
UNION ALL
SELECT 'Solo en COPIA', producto_id, nombre, precio FROM
(SELECT producto_id, nombre, precio FROM productos_copia
MINUS
SELECT producto_id, nombre, precio FROM productos)
ORDER BY producto_id, donde;
DROP TABLE productos_copia PURGE;
Tabla creada.
1 fila actualizada.
1 fila suprimida.
1 fila creada.
Confirmación terminada.
DONDE PRODUCTO_ID NOMBRE PRECIO
----------------- ----------- --------------------------- ----------
Solo en COPIA 1 Portátil UltraBook 14 949
Solo en PRODUCTOS 1 Portátil UltraBook 14 899
Solo en PRODUCTOS 14 Altavoz Inteligente 49
Solo en COPIA 21 Hub USB-C 39,9
4 filas seleccionadas.
Tabla borrada.
Para comparar todas las columnas, usa SELECT * en las cuatro consultas (con cuidado: el orden de las columnas debe coincidir, y CREATE TABLE ... AS SELECT * lo garantiza).
Ejercicio 9: Pedidos por estado con total
Muestra el número de pedidos de cada estado y una última fila con el total.
Ver solución
SELECT estado, COUNT(*) AS pedidos
FROM pedidos
GROUP BY estado
UNION ALL
SELECT 'TOTAL', COUNT(*)
FROM pedidos
ORDER BY pedidos;
ESTADO PEDIDOS
------------ ----------
CANCELADO 1
ENVIADO 2
PENDIENTE 3
ENTREGADO 18
TOTAL 24
5 filas seleccionadas.
Ejercicio 10: Oficios en común contando repeticiones
¿Qué oficios de VENTAS (20) coinciden con oficios de CONTABILIDAD o LOGÍSTICA (40, 60), contando cada coincidencia una vez por empleado? Y a la inversa, ¿qué “puestos” de 40 y 60 sobran tras emparejarlos con los de 20?
Ver solución
SELECT oficio FROM empleados WHERE dept_id = 20
INTERSECT ALL
SELECT oficio FROM empleados WHERE dept_id IN (40, 60);
SELECT oficio FROM empleados WHERE dept_id IN (40, 60)
EXCEPT ALL
SELECT oficio FROM empleados WHERE dept_id = 20
ORDER BY 1;
OFICIO
-----------------
DIRECTOR
ADMINISTRATIVO
2 filas seleccionadas.
OFICIO
-----------------
ADMINISTRATIVO
CONTABLE
DIRECTOR
OPERARIO
OPERARIO
5 filas seleccionadas.
VENTAS tiene 1 director y 1 administrativo; 40 y 60 tienen 2 directores y 2 administrativos: el mínimo es 1 de cada (INTERSECT ALL). Al restar quedan 1 director, 1 administrativo, el contable y los 2 operarios. Requiere Oracle 21c o posterior.
Ejercicio 11: Altas recientes ordenadas por posición
Muestra los empleados dados de alta desde 2023 y los clientes dados de alta desde 2024, con nombre, fecha de alta y tipo, ordenados por fecha descendente usando la posición de la columna.
Ver solución
SELECT nombre, fecha_alta, 'Empleado' AS tipo FROM empleados WHERE fecha_alta >= DATE '2023-01-01'
UNION ALL
SELECT nombre, fecha_alta, 'Cliente' FROM clientes WHERE fecha_alta >= DATE '2024-01-01'
ORDER BY 2 DESC;
NOMBRE FECHA_ALTA TIPO
--------------------------- ---------- ---------
Tomás 01/09/2025 Empleado
Farmacia Central 12/06/2025 Cliente
Carlos Ibáñez Rey 08/01/2025 Cliente
Diseños Creativos S.L. 05/05/2024 Cliente
Panadería La Espiga 20/01/2024 Cliente
Jorge 09/01/2023 Empleado
6 filas seleccionadas.
Ejercicio 12: Corregir una unión incompatible
Explica el error de esta consulta y corrígela para que muestre el nombre y “un dato” (fecha de alta o límite de crédito) como texto:
SELECT nombre, fecha_alta FROM empleados WHERE dept_id = 40
UNION
SELECT nombre, limite_credito FROM clientes WHERE ciudad = 'Oviedo';
Ver solución
SELECT nombre, fecha_alta FROM empleados WHERE dept_id = 40
*
ERROR en línea 1:
ORA-01790: expression must have same datatype as corresponding expression
Help: https://docs.oracle.com/error-help/db/ora-01790/
La segunda columna es DATE en la primera rama y NUMBER en la segunda. Corrección convirtiendo ambas a texto:
SELECT nombre, TO_CHAR(fecha_alta, 'DD/MM/YYYY') AS dato FROM empleados WHERE dept_id = 40
UNION
SELECT nombre, TO_CHAR(limite_credito) FROM clientes WHERE ciudad = 'Oviedo';
NOMBRE DATO
--------------------------- ------------
Pedro 10/09/2013
Isabel 08/08/2016
Jorge 09/01/2023
Farmacia Central 4000
4 filas seleccionadas.
Sin ORDER BY el orden no está garantizado. Mezclar datos de distinta naturaleza en una columna rara vez es buena idea en un informe real.
Ejercicio 13: Los tres pedidos más grandes
Con una CTE para los importes, muestra los tres pedidos de mayor importe con el nombre del cliente y la fecha.
Ver solución
WITH importes AS (
SELECT pedido_id,
ROUND(SUM(cantidad * precio_unitario * (1 - descuento / 100)), 2) AS importe
FROM lineas_pedido
GROUP BY pedido_id
)
SELECT i.pedido_id, c.nombre AS cliente, p.fecha_pedido, i.importe
FROM importes i
JOIN pedidos p ON p.pedido_id = i.pedido_id
JOIN clientes c ON c.cliente_id = p.cliente_id
ORDER BY i.importe DESC
FETCH FIRST 3 ROWS ONLY;
PEDIDO_ID CLIENTE FECHA_PEDI IMPORTE
---------- --------------------------- ---------- ----------
5010 Instituto Tecnológico Sur 02/06/2025 11657,5
5016 Instituto Tecnológico Sur 12/01/2026 8945,05
5002 Construcciones Levante S.A. 28/01/2025 7402,5
3 filas seleccionadas.
Ejercicio 14: Masas salariales por encima de la media
Con una CTE, muestra los departamentos cuya masa salarial supera la media de las masas salariales.
Ver solución
WITH masas AS (
SELECT dept_id, SUM(salario) AS masa
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
)
SELECT d.nombre AS departamento, m.masa
FROM masas m
JOIN departamentos d ON d.dept_id = m.dept_id
WHERE m.masa > (SELECT AVG(masa) FROM masas)
ORDER BY m.masa DESC;
DEPARTAMENTO MASA
-------------- ----------
INFORMÁTICA 18800
VENTAS 15000
2 filas seleccionadas.
La media de las masas es 67 000 / 6 = 11 166,67 €. La CTE se usa dos veces.
Ejercicio 15: El más vendido de cada categoría
Con dos CTE encadenadas (unidades vendidas por producto y producto con su categoría), muestra el producto más vendido en unidades de cada categoría.
Ver solución
WITH unidades AS (
SELECT producto_id, SUM(cantidad) AS uds
FROM lineas_pedido
GROUP BY producto_id
),
con_categoria AS (
SELECT p.categoria_id, p.nombre, u.uds
FROM productos p
JOIN unidades u ON u.producto_id = p.producto_id
)
SELECT cc.categoria_id, cc.nombre AS producto, cc.uds
FROM con_categoria cc
WHERE cc.uds = (SELECT MAX(x.uds) FROM con_categoria x WHERE x.categoria_id = cc.categoria_id)
ORDER BY cc.categoria_id;
CATEGORIA_ID PRODUCTO UDS
------------ ---------------------- ----------
1 Disco SSD 1TB 14
2 Portátil Básico 15 24
3 Torre Oficina i5 11
4 Webcam HD 9
5 Pack teclado y ratón 30
6 Monitor 24 FHD 40
7 Tablet 10 3
8 Smartphone Gama Media 11
9 Auriculares Bluetooth 10
11 Silla Ergonómica 7
10 filas seleccionadas.
La subconsulta correlacionada puede usar la CTE como cualquier otra tabla.
Ejercicio 16: Clientes que crecen
Muestra los clientes cuya facturación (pedidos no cancelados) de 2026 supera la de 2025.
Ver solución
WITH ventas_anio AS (
SELECT p.cliente_id,
EXTRACT(YEAR FROM p.fecha_pedido) AS anio,
SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)) AS total
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
GROUP BY p.cliente_id, EXTRACT(YEAR FROM p.fecha_pedido)
)
SELECT c.nombre AS cliente, ROUND(v25.total, 2) AS total_2025, ROUND(v26.total, 2) AS total_2026
FROM ventas_anio v25
JOIN ventas_anio v26 ON v26.cliente_id = v25.cliente_id AND v26.anio = 2026
JOIN clientes c ON c.cliente_id = v25.cliente_id
WHERE v25.anio = 2025
AND v26.total > v25.total
ORDER BY c.nombre;
CLIENTE TOTAL_2025 TOTAL_2026
--------------------------- ---------- ----------
Clínica Dental Sonrisa 936 1296
Laura Benítez Sanz 79,8 418,8
Talleres Martín S.L. 2242,1 2462,05
3 filas seleccionadas.
Ejercicio 17: Objetivos de los comerciales
Con una CTE construida con VALUES (Oracle 23ai) define estos objetivos de ventas: 110 → 20 000, 111 → 15 000, 112 → 20 000, 113 → 5 000. Muestra, para cada comercial, el objetivo, lo vendido (pedidos no cancelados de sus clientes) y el porcentaje cumplido.
Ver solución
WITH objetivos (comercial_id, objetivo) AS (
VALUES (110, 20000), (111, 15000), (112, 20000), (113, 5000)
),
real AS (
SELECT c.comercial_id,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS vendido
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.cliente_id
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
GROUP BY c.comercial_id
)
SELECT e.nombre, o.objetivo, r.vendido,
ROUND(100 * r.vendido / o.objetivo, 1) AS pct_cumplido
FROM objetivos o
JOIN empleados e ON e.emp_id = o.comercial_id
LEFT JOIN real r ON r.comercial_id = o.comercial_id
ORDER BY pct_cumplido DESC;
NOMBRE OBJETIVO VENDIDO PCT_CUMPLIDO
--------------------------- ---------- ---------- ------------
Elena 20000 23833,55 119,2
Carmen 20000 22018,34 110,1
David 15000 12931,97 86,2
Sergio 5000 2335 46,7
4 filas seleccionadas.
En 19c, sustituye el VALUES por cuatro SELECT ... FROM dual unidos con UNION ALL.
Ejercicio 18: Reparto de horas por proyecto
Con dos CTE, calcula las horas totales de cada proyecto y el porcentaje que suponen sobre el total de horas de todos los proyectos.
Ver solución
WITH horas_proy AS (
SELECT proyecto_id, SUM(horas) AS horas
FROM asignaciones
GROUP BY proyecto_id
),
total AS (
SELECT SUM(horas) AS horas FROM horas_proy
)
SELECT p.nombre AS proyecto, h.horas,
ROUND(100 * h.horas / t.horas, 1) AS pct
FROM horas_proy h
JOIN proyectos p ON p.proyecto_id = h.proyecto_id
CROSS JOIN total t
ORDER BY h.horas DESC;
PROYECTO HORAS PCT
------------------------ ---------- ----------
Migración a la nube 1430 40,2
Nueva tienda online 1350 37,9
Campaña Navidad 2025 480 13,5
Optimización de almacén 300 8,4
4 filas seleccionadas.
total tiene una sola fila, así que el CROSS JOIN la “pega” a cada proyecto. En el módulo 33 lo harás más fácilmente con RATIO_TO_REPORT.
Ejercicio 19: La tabla del 7
Genera con una CTE recursiva la tabla de multiplicar del 7 (del 1 al 10).
Ver solución
WITH tabla (n) AS (
SELECT 1 FROM dual
UNION ALL
SELECT n + 1 FROM tabla WHERE n < 10
)
SELECT '7 x ' || n || ' = ' || 7 * n AS tabla_del_7
FROM tabla;
TABLA_DEL_7
---------------
7 x 1 = 7
7 x 2 = 14
7 x 3 = 21
7 x 4 = 28
7 x 5 = 35
7 x 6 = 42
7 x 7 = 49
7 x 8 = 56
7 x 9 = 63
7 x 10 = 70
10 filas seleccionadas.
Ejercicio 20: Días laborables de octubre de 2026
Genera todos los días de octubre de 2026 y cuenta cuántos son sábado o domingo y cuántos laborables (de lunes a viernes, sin tener en cuenta festivos).
Ver solución
WITH dias (dia) AS (
SELECT DATE '2026-10-01' FROM dual
UNION ALL
SELECT dia + 1 FROM dias WHERE dia < DATE '2026-10-31'
)
SELECT COUNT(*) AS dias_mes,
COUNT(CASE WHEN TO_CHAR(dia, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN 1 END) AS fin_de_semana,
COUNT(CASE WHEN TO_CHAR(dia, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN') THEN 1 END) AS laborables
FROM dias;
DIAS_MES FIN_DE_SEMANA LABORABLES
---------- ------------- ----------
31 9 22
1 fila seleccionada.
Se fija el idioma con el tercer parámetro de TO_CHAR para que la consulta funcione igual en cualquier configuración. No uses el formato 'D' (número de día de la semana): depende del territorio de la sesión (en España el lunes es 1; en EE. UU., el domingo), y TO_CHAR no admite fijar NLS_TERRITORY en su tercer parámetro (da ORA-12702).
Ejercicio 21: El equipo de Marta con su jefe inmediato
Lista todos los subordinados (directos e indirectos) de Marta Sánchez (102), sin incluirla, con el nombre de su jefe inmediato y el nivel relativo (1 = directos).
Ver solución
WITH equipo (emp_id, nombre, oficio, jefe, nivel) AS (
SELECT e.emp_id, e.nombre, e.oficio, CAST('Marta' AS VARCHAR2(30)), 1
FROM empleados e
WHERE e.jefe_id = 102
UNION ALL
SELECT e.emp_id, e.nombre, e.oficio, q.nombre, q.nivel + 1
FROM empleados e
JOIN equipo q ON e.jefe_id = q.emp_id
)
SELECT emp_id, nombre, oficio, jefe, nivel
FROM equipo
ORDER BY nivel, nombre;
EMP_ID NOMBRE OFICIO JEFE NIVEL
---------- --------------------------- ----------------- ---------- ----------
124 Hugo TÉCNICO SISTEMAS Marta 1
120 Pablo ANALISTA Marta 1
160 Tomás BECARIO Marta 1
122 Iván PROGRAMADOR Pablo 2
121 Raquel PROGRAMADOR Pablo 2
123 Sara PROGRAMADOR Pablo 2
6 filas seleccionadas.
El ancla empieza directamente en los subordinados directos (jefe_id = 102). El nombre del jefe inmediato es el nombre de la fila padre en la recursión (q.nombre).
Ejercicio 22: Subordinados directos y totales
Para cada empleado que tenga subordinados, muestra cuántos tiene directos y cuántos en total (directos e indirectos).
Ver solución
WITH bajo (raiz_id, emp_id) AS (
SELECT emp_id, emp_id FROM empleados
UNION ALL
SELECT b.raiz_id, e.emp_id
FROM empleados e
JOIN bajo b ON e.jefe_id = b.emp_id
)
SELECT j.nombre, j.oficio,
(SELECT COUNT(*) FROM empleados d WHERE d.jefe_id = j.emp_id) AS directos,
COUNT(*) - 1 AS totales
FROM bajo b
JOIN empleados j ON j.emp_id = b.raiz_id
GROUP BY j.emp_id, j.nombre, j.oficio
HAVING COUNT(*) > 1
ORDER BY totales DESC, j.nombre;
NOMBRE OFICIO DIRECTOS TOTALES
--------------------------- ----------------- ---------- ----------
Ana DIRECTOR GENERAL 5 23
Marta DIRECTOR 3 6
Luis DIRECTOR 5 5
Javier DIRECTOR 3 3
Pablo ANALISTA 3 3
Laura DIRECTOR 2 2
Pedro DIRECTOR 2 2
7 filas seleccionadas.
Se resta 1 porque cada raíz se cuenta a sí misma (fila del ancla). La subconsulta escalar del SELECT puede usar j.emp_id porque está en el GROUP BY.
Ejercicio 23: Masa salarial por subárbol
Muestra, para cada empleado con subordinados, la suma de su salario y el de todos los que tiene por debajo. (Resuelto en la sección 10.5; vuelve a escribirlo sin mirar.)
Ver solución
WITH bajo_jefe (raiz_id, emp_id, salario) AS (
SELECT emp_id, emp_id, salario
FROM empleados
UNION ALL
SELECT b.raiz_id, e.emp_id, e.salario
FROM empleados e
JOIN bajo_jefe b ON e.jefe_id = b.emp_id
)
SELECT j.emp_id, j.nombre, j.oficio,
COUNT(*) - 1 AS subordinados,
SUM(b.salario) AS masa_subarbol
FROM bajo_jefe b
JOIN empleados j ON j.emp_id = b.raiz_id
GROUP BY j.emp_id, j.nombre, j.oficio
HAVING COUNT(*) > 1
ORDER BY masa_subarbol DESC;
EMP_ID NOMBRE OFICIO SUBORDINADOS MASA_SUBARBOL
---------- --------------------------- ----------------- ------------ -------------
100 Ana DIRECTOR GENERAL 23 67900
102 Marta DIRECTOR 6 19700
101 Luis DIRECTOR 5 15000
120 Pablo ANALISTA 3 11300
104 Laura DIRECTOR 2 9300
105 Javier DIRECTOR 3 8950
103 Pedro DIRECTOR 2 8450
7 filas seleccionadas.
La clave es que el ancla incluye a todos los empleados como posibles raíces, y la columna raiz_id se copia sin cambios en el miembro recursivo.
Ejercicio 24: Ruta completa de categoría de los productos caros
Para los productos de más de 300 €, muestra el nombre y la ruta completa de su categoría (por ejemplo, Informática / Periféricos / Monitores).
Ver solución
WITH rutas (categoria_id, ruta) AS (
SELECT categoria_id, CAST(nombre AS VARCHAR2(200))
FROM categorias
WHERE categoria_padre_id IS NULL
UNION ALL
SELECT c.categoria_id, r.ruta || ' / ' || c.nombre
FROM categorias c
JOIN rutas r ON c.categoria_padre_id = r.categoria_id
)
SELECT p.nombre AS producto, r.ruta
FROM productos p
JOIN rutas r ON r.categoria_id = p.categoria_id
WHERE p.precio > 300
ORDER BY r.ruta, p.nombre;
PRODUCTO RUTA
---------------------- --------------------------------------------------
Smartphone Gama Alta Electrónica / Telefonía
Torre Oficina i5 Informática / Ordenadores sobremesa
Torre Workstation Informática / Ordenadores sobremesa
Monitor 27 4K Informática / Periféricos / Monitores
Portátil Básico 15 Informática / Portátiles
Portátil Gaming 16 Informática / Portátiles
Portátil UltraBook 14 Informática / Portátiles
Mesa Elevable Oficina / Mobiliario
8 filas seleccionadas.
Ejercicio 25: Cadena de mando en una línea
Muestra en una sola fila la cadena de mando de Tomás Reyes (160) hasta la cima, con el formato Tomás -> Marta -> Ana.
Ver solución
WITH cadena (emp_id, nombre, jefe_id, paso) AS (
SELECT emp_id, nombre, jefe_id, 0
FROM empleados
WHERE emp_id = 160
UNION ALL
SELECT j.emp_id, j.nombre, j.jefe_id, c.paso + 1
FROM empleados j
JOIN cadena c ON j.emp_id = c.jefe_id
)
SELECT LISTAGG(nombre, ' -> ') WITHIN GROUP (ORDER BY paso) AS cadena
FROM cadena;
CADENA
----------------------------------------
Tomás -> Marta -> Ana
1 fila seleccionada.
La recursión sube por la jerarquía y LISTAGG (módulo 29) concatena las filas en el orden del paso.
Ejercicio 26: Empleados por nivel jerárquico
Cuenta cuántos empleados hay en cada nivel del organigrama.
Ver solución
WITH jerarquia (emp_id, nombre, dept_id, nivel) AS (
SELECT emp_id, nombre, dept_id, 1 FROM empleados WHERE jefe_id IS NULL
UNION ALL
SELECT e.emp_id, e.nombre, e.dept_id, j.nivel + 1
FROM empleados e
JOIN jerarquia j ON e.jefe_id = j.emp_id
)
SELECT nivel, COUNT(*) AS empleados
FROM jerarquia
GROUP BY nivel
ORDER BY nivel;
NIVEL EMPLEADOS
---------- ----------
1 1
2 5
3 15
4 3
4 filas seleccionadas.
Ejercicio 27: Categorías en anchura
Lista las categorías en recorrido en anchura (primero las raíces, luego sus hijas…), ordenando alfabéticamente dentro de cada nivel.
Ver solución
WITH arbol (categoria_id, nombre, nivel) AS (
SELECT categoria_id, nombre, 1 FROM categorias WHERE categoria_padre_id IS NULL
UNION ALL
SELECT c.categoria_id, c.nombre, a.nivel + 1
FROM categorias c
JOIN arbol a ON c.categoria_padre_id = a.categoria_id
)
SEARCH BREADTH FIRST BY nombre SET orden
SELECT orden, nivel, nombre AS categoria
FROM arbol
ORDER BY orden;
ORDEN NIVEL CATEGORIA
---------- ---------- ----------------------
1 1 Electrónica
2 1 Informática
3 1 Oficina
4 2 Audio
5 2 Mobiliario
6 2 Ordenadores sobremesa
7 2 Periféricos
8 2 Portátiles
9 2 Telefonía
10 3 Monitores
11 3 Ratones y teclados
11 filas seleccionadas.
Ejercicio 28: Destinos alcanzables con ciclos
Con la tabla rutas_demo de la sección 10.7 (si la has borrado, créala de nuevo con aquel script), lista todas las ciudades alcanzables desde Valencia, con el camino, sin incluir la propia Valencia y sin entrar en bucle. Bórrala al final.
Ver solución
WITH viaje (destino, camino) AS (
SELECT destino, CAST(origen || ' > ' || destino AS VARCHAR2(200))
FROM rutas_demo
WHERE origen = 'Valencia'
UNION ALL
SELECT r.destino, v.camino || ' > ' || r.destino
FROM rutas_demo r
JOIN viaje v ON r.origen = v.destino
)
CYCLE destino SET es_ciclo TO 'S' DEFAULT 'N'
SELECT destino, camino
FROM viaje
WHERE es_ciclo = 'N'
AND destino <> 'Valencia';
DROP TABLE rutas_demo PURGE;
DESTINO CAMINO
---------- -------------------------------------------------------
Sevilla Valencia > Sevilla
Madrid Valencia > Sevilla > Madrid
Bilbao Valencia > Sevilla > Bilbao
3 filas seleccionadas.
Tabla borrada.
Sin los filtros, la CTE devuelve también el camino Valencia > Sevilla > Madrid > Valencia (marcado N, porque Valencia no había sido el destino de ninguna fila anterior de esa rama: el origen del ancla no es una fila) y Valencia > Sevilla > Madrid > Valencia > Sevilla (marcado S, porque Sevilla se repite). La condición destino <> 'Valencia' elimina el primero y es_ciclo = 'N' el segundo.
Ejercicio 29: Fibonacci
Genera los 15 primeros términos de la sucesión de Fibonacci (0, 1, 1, 2, 3, 5…) en una sola fila.
Ver solución
WITH fib (n, actual, siguiente) AS (
SELECT 1, 0, 1 FROM dual
UNION ALL
SELECT n + 1, siguiente, actual + siguiente FROM fib WHERE n < 15
)
SELECT LISTAGG(actual, ', ') WITHIN GROUP (ORDER BY n) AS fibonacci
FROM fib;
FIBONACCI
------------------------------------------------------------
0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377
1 fila seleccionada.
El “estado” de la recursión son dos columnas: el término actual y el siguiente. Cada paso desplaza la ventana: (a, b) → (b, a + b).
Ejercicio 30: Meses de 2026 sin pedidos
Genera los doce meses de 2026 y muestra los que no tienen ningún pedido.
Ver solución
WITH meses (mes) AS (
SELECT DATE '2026-01-01' FROM dual
UNION ALL
SELECT ADD_MONTHS(mes, 1) FROM meses WHERE mes < DATE '2026-12-01'
)
SELECT TO_CHAR(m.mes, 'YYYY-MM') AS mes
FROM meses m
WHERE NOT EXISTS (SELECT 1
FROM pedidos p
WHERE TRUNC(p.fecha_pedido, 'MM') = m.mes)
ORDER BY m.mes;
MES
-------
2026-08
2026-09
2026-10
2026-11
2026-12
5 filas seleccionadas.
Generar el “universo” con una CTE y restarle lo que existe (con NOT EXISTS o MINUS) es la técnica general para encontrar huecos.
Ejercicio 31: Ventas trimestrales con total al final
Muestra las ventas de cada trimestre de 2025 (pedidos no cancelados) y una fila de total, que debe salir la última.
Ver solución
Un primer intento con ORDER BY 1 pone el total delante: con la ordenación lingüística española de la sesión (NLS_SORT = SPANISH), las letras van antes que los dígitos y 'TOTAL 2025' precede a 'T1'. Y no se puede ordenar por una expresión (ORA-01785). La solución es añadir una columna de orden y ordenar en una consulta exterior:
WITH ventas AS (
SELECT 'T' || TO_CHAR(p.fecha_pedido, 'Q') AS trimestre,
l.cantidad * l.precio_unitario * (1 - l.descuento / 100) AS importe
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
AND p.fecha_pedido >= DATE '2025-01-01'
AND p.fecha_pedido < DATE '2026-01-01'
)
SELECT trimestre, ventas
FROM (SELECT trimestre, ROUND(SUM(importe), 2) AS ventas, 1 AS orden
FROM ventas
GROUP BY trimestre
UNION ALL
SELECT 'TOTAL 2025', ROUND(SUM(importe), 2), 2
FROM ventas)
ORDER BY orden, trimestre;
TRIMESTRE VENTAS
---------- ----------
T1 17372
T2 19968,07
T3 1658,8
T4 1913,8
TOTAL 2025 40912,67
5 filas seleccionadas.
La CTE ventas se usa dos veces (para el detalle y para el total).
Ejercicio 32: Antepasados de una categoría
Muestra la cadena de categorías desde la raíz hasta “Ratones y teclados”.
Ver solución
WITH sube (categoria_id, nombre, padre_id, paso) AS (
SELECT categoria_id, nombre, categoria_padre_id, 0
FROM categorias
WHERE nombre = 'Ratones y teclados'
UNION ALL
SELECT c.categoria_id, c.nombre, c.categoria_padre_id, s.paso + 1
FROM categorias c
JOIN sube s ON c.categoria_id = s.padre_id
)
SELECT paso, categoria_id, nombre AS categoria
FROM sube
ORDER BY paso DESC;
PASO CATEGORIA_ID CATEGORIA
---------- ------------ ----------------------
2 1 Informática
1 4 Periféricos
0 5 Ratones y teclados
3 filas seleccionadas.
Se sube desde la hoja (paso 0) y se ordena de forma descendente para mostrar la raíz primero.
Ejercicios propuestos
Ejercicio 33: Directorio de correos
Obtén un listado único de direcciones de correo electrónico de empleados y clientes (sin nulos), con una columna que indique el origen, ordenado por dominio.
Ejercicio 34: Ciudades sin departamento
Lista las ciudades donde hay clientes pero no hay ningún departamento, y las localidades con departamento pero sin clientes.
Ejercicio 35: Productos nunca vendidos con conjuntos
Obtén los productos que nunca se han vendido usando MINUS y compara el resultado con el de NOT EXISTS del módulo 31 usando la técnica de la diferencia simétrica (debe dar cero filas).
Ejercicio 36: Precedencia
Escribe tres consultas con UNION, INTERSECT y MINUS combinados de forma que el resultado cambie según dónde se pongan los paréntesis, y explica cada resultado con notación de conjuntos.
Ejercicio 37: Informe de proyectos con CTE
Con varias CTE encadenadas, muestra para cada proyecto: nombre, departamento, número de empleados asignados, horas totales, coste estimado (horas × salario / 160 de cada empleado) y porcentaje del presupuesto consumido.
Ejercicio 38: Clientes por tramo de facturación
Con una CTE de facturación por cliente y otra creada con VALUES que defina tramos (0-1000, 1000-5000, 5000-15000, más de 15000), cuenta cuántos clientes hay en cada tramo, incluidos los tramos vacíos.
Ejercicio 39: Nivel máximo de la jerarquía de categorías
Calcula con una CTE recursiva la profundidad máxima del árbol de categorías y lista las categorías hoja (sin hijas) con su nivel.
Ejercicio 40: Calendario de semanas
Genera las semanas (lunes a domingo) de julio de 2026 y muestra, para cada una, el número de pedidos realizados.
Ejercicio 41: Huecos en una numeración
Crea una tabla facturas_demo con los números 1, 2, 3, 5, 6, 9, 10. Con una serie generada recursivamente, encuentra los números que faltan. Borra la tabla al final.
Ejercicio 42: Organigrama de un departamento
Muestra con sangría y orden en profundidad el organigrama de los empleados del departamento 30, empezando por su director.
Ejercicio 43: Distancias en un grafo
Crea una tabla de rutas con distancias en kilómetros entre cinco ciudades (con al menos un ciclo) y calcula, con una CTE recursiva con CYCLE, la distancia total de cada camino posible desde una ciudad, y el camino más corto a cada destino.
Ejercicio 44: Factorial
Calcula con una CTE recursiva el factorial de los números del 1 al 10.
Resumen
| Concepto | Sintaxis / idea clave |
|---|---|
UNION | Filas de ambas consultas, sin duplicados (∪) |
UNION ALL | Concatena, conserva duplicados; más eficiente |
INTERSECT | Filas comunes (∩) |
MINUS / EXCEPT | Filas de la primera que no están en la segunda (−); EXCEPT desde 21c |
INTERSECT ALL, MINUS ALL, EXCEPT ALL | Versiones de multiconjunto (21c+): cuentan apariciones |
| Compatibilidad | Mismo nº de columnas y tipos compatibles por posición; nombres de la 1.ª consulta |
ORDER BY | Uno solo, al final; por nombre/alias de la 1.ª consulta o por posición |
NULL en conjuntos | Dos NULL se consideran iguales |
| Precedencia en Oracle | Todos iguales, de izquierda a derecha: usa paréntesis |
| Diferencias entre tablas | (A MINUS B) UNION ALL (B MINUS A) |
WITH (CTE) | WITH nombre [(cols)] AS (SELECT ...) SELECT ... FROM nombre |
| CTE encadenadas | Cada CTE puede usar las anteriores; se leen de arriba abajo |
| CTE recursiva | Ancla UNION ALL miembro recursivo; lista de columnas obligatoria |
| Jerarquía hacia abajo / arriba | e.jefe_id = cte.emp_id / e.emp_id = cte.jefe_id |
SEARCH DEPTH FIRST BY c SET o | Orden de árbol (profundidad) |
SEARCH BREADTH FIRST BY c SET o | Orden por niveles (anchura) |
CYCLE c SET marca TO 'S' DEFAULT 'N' | Detecta y corta ciclos |
| Series | SELECT 1 FROM dual UNION ALL SELECT n + 1 FROM cte WHERE n < N |
VALUES | Constructor de tabla con literales (23ai) |