Módulo 35: Subconsultas
Objetivos del módulo
- Comprender qué es una subconsulta (subquery), cómo la evalúa Oracle y en qué cláusulas puede aparecer
- Distinguir subconsultas escalares, de fila y de tabla, y no correlacionadas frente a correlacionadas
- Reconocer y corregir los errores típicos:
ORA-01427,ORA-00913,ORA-00907 - Comparar por pares con
(a, b) = (SELECT ...)y(a, b) IN (SELECT ...) - Usar con seguridad
IN,NOT IN,ANY/SOME,ALL,EXISTSyNOT EXISTS, entendiendo su comportamiento conNULLy con conjuntos vacíos - Escribir subconsultas correlacionadas, vistas en línea (tablas derivadas) y subconsultas escalares en el
SELECT - Decidir entre subconsulta y
JOIN, y saber qué hace el optimizador con cada una - Resolver consultas de tipo “para todos” con la división relacional y el doble
NOT EXISTS
1. ¿Qué es una subconsulta?
📘 Concepto: Una subconsulta (subquery, también llamada consulta anidada o nested query) es una sentencia
SELECTescrita entre paréntesis dentro de otra sentencia SQL. La sentencia que la contiene se llama consulta externa o principal (outer query) y la subconsulta, consulta interna (inner query).
Piensa en una pregunta como esta: “¿Qué empleados cobran más que Pablo Moreno?”. Para responderla necesitas antes otro dato: ¿cuánto cobra Pablo? Una persona lo haría en dos pasos: mirar el sueldo de Pablo, apuntarlo en un papel y luego buscar a quien cobre más. Una subconsulta es exactamente ese “papel”: una pregunta auxiliar cuya respuesta se usa dentro de la pregunta principal, todo en una única sentencia.
Primer paso, la pregunta auxiliar por separado:
SELECT salario
FROM empleados
WHERE emp_id = 120;
SALARIO
----------
3500
1 fila seleccionada.
Segundo paso, la pregunta completa con la auxiliar incrustada:
SELECT nombre, apellidos, salario
FROM empleados
WHERE salario > (SELECT salario
FROM empleados
WHERE emp_id = 120)
ORDER BY salario DESC;
NOMBRE APELLIDOS SALARIO
--------------------------- ------------------ ----------
Ana García López 6500
Marta Sánchez Gil 5000
Luis Martínez Ruiz 4800
Laura Fernández Moreno 4300
Pedro Gómez Díaz 4200
Javier López Navarro 4000
6 filas seleccionadas.
La ventaja es enorme: si mañana a Pablo le suben el sueldo, la consulta sigue siendo correcta sin tocarla. Nunca escribas a mano un valor (WHERE salario > 3500) que puedes obtener de la propia base de datos.
Otro ejemplo clásico: el empleado que más cobra. No sabemos cuál es el máximo, así que lo calculamos dentro:
SELECT nombre, apellidos, salario
FROM empleados
WHERE salario = (SELECT MAX(salario) FROM empleados);
NOMBRE APELLIDOS SALARIO
--------------------------- ------------------ ----------
Ana García López 6500
1 fila seleccionada.
⚠️ Importante: No se puede escribir
WHERE salario = MAX(salario): las funciones de agregado no pueden aparecer en elWHERE(se evalúa fila a fila, antes de agrupar). La subconsulta es la forma correcta de “traer” un agregado alWHERE.
1.1. Reglas generales
- La subconsulta va siempre entre paréntesis.
- Puede ir a cualquier lado del operador, aunque por legibilidad se escribe a la derecha.
- Una subconsulta en
WHEREoHAVINGno puede llevarORDER BY(no tiene sentido ordenar un conjunto que solo se usa para comparar). Oracle lo rechaza con un error poco descriptivo:
SELECT nombre
FROM empleados
WHERE salario = (SELECT MAX(salario) FROM empleados ORDER BY 1);
WHERE salario = (SELECT MAX(salario) FROM empleados ORDER BY 1)
*
ERROR en línea 3:
ORA-00907: missing right parenthesis
Help: https://docs.oracle.com/error-help/db/ora-00907/
- Las subconsultas pueden anidarse unas dentro de otras (Oracle admite hasta 255 niveles en el
WHERE, muchísimo más de lo que necesitarás). - Una subconsulta puede usar columnas de la consulta externa: entonces es correlacionada (sección 8).
💡 Consejo: Los mensajes de error de esta edición de Oracle aparecen en inglés aunque la sesión esté en español. Los números
ORA-xxxxxson los que importan: búscalos en la documentación (el propio mensaje incluye el enlaceHelp:).
1.2. Clasificación de las subconsultas
Hay dos criterios independientes para clasificarlas:
| Criterio | Tipo | Qué devuelve / cómo se evalúa | Operadores con los que se usa |
|---|---|---|---|
| Forma del resultado | Escalar (scalar) | Una fila y una columna (un único valor) | =, <>, <, >, <=, >=, aritmética, en el SELECT |
| Forma del resultado | De fila (row subquery) | Una fila y varias columnas | (a, b) = (...), (a, b) <> (...) |
| Forma del resultado | De tabla o multifila (table subquery) | Varias filas (una o más columnas) | IN, NOT IN, ANY, ALL, EXISTS, en el FROM |
| Dependencia | No correlacionada | Se puede ejecutar sola; conceptualmente se evalúa una vez | Cualquiera |
| Dependencia | Correlacionada (correlated) | Usa columnas de la consulta externa; conceptualmente se evalúa una vez por cada fila externa | Cualquiera, típica con EXISTS |
En álgebra relacional (módulo 18) no existen las subconsultas como tales: cada subconsulta equivale a una expresión que se combina con la principal mediante selección, semicombinación o diferencia. Por ejemplo, “empleados que cobran más que Pablo” es:
P ← ρ[P](π[salario](σ[emp_id = 120](EMPLEADOS)))
π[nombre, apellidos, EMPLEADOS.salario](σ[EMPLEADOS.salario > P.salario](EMPLEADOS × P))
SQL permite escribirlo de forma mucho más natural, que es precisamente para lo que sirven las subconsultas.
2. Dónde puede aparecer una subconsulta
| Lugar | Nombre habitual | Ejemplo de uso |
|---|---|---|
WHERE | Subconsulta de filtro | Empleados que cobran más que la media |
HAVING | Subconsulta de filtro de grupos | Departamentos cuya media supera la media de la empresa |
FROM | Vista en línea (inline view) o tabla derivada | Calcular totales por pedido y luego filtrarlos |
SELECT | Subconsulta escalar | Mostrar junto a cada empleado la media de su departamento |
ORDER BY | Subconsulta escalar de ordenación | Ordenar departamentos por número de empleados |
INSERT, UPDATE, DELETE, MERGE | Subconsulta en DML | Subir el sueldo a los de un departamento buscado por nombre (módulo 34) |
CREATE TABLE ... AS SELECT | CTAS | Crear una tabla a partir de una consulta |
WITH | CTE (common table expression) | Darle nombre a una subconsulta (módulo 32) |
Veamos un ejemplo de cada una. Las secciones siguientes las desarrollan en profundidad.
2.1. En HAVING
Departamentos cuyo salario medio supera el salario medio de toda la empresa. El HAVING filtra grupos, y la subconsulta aporta el valor de referencia:
SELECT dept_id, ROUND(AVG(salario), 2) AS media_dept
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING AVG(salario) > (SELECT AVG(salario) FROM empleados)
ORDER BY dept_id;
DEPT_ID MEDIA_DEPT
---------- ----------
10 6500
30 3133,33
50 3100
3 filas seleccionadas.
La media de referencia es:
SELECT ROUND(AVG(salario), 2) AS media_empresa FROM empleados;
MEDIA_EMPRESA
-------------
2829,17
1 fila seleccionada.
💡 Consejo: Los decimales aparecen con coma porque la sesión de SQL*Plus usa el territorio
SPAIN(NLS_NUMERIC_CHARACTERS = ',.'). En el código SQL los literales numéricos se escriben siempre con punto (1.05).
2.2. En FROM (vista en línea)
Una subconsulta en el FROM se comporta como una tabla temporal que solo existe durante la consulta. Aquí primero contamos empleados por oficio y luego filtramos el resultado como si fuera una tabla:
SELECT oficio, num_empleados
FROM (SELECT oficio, COUNT(*) AS num_empleados
FROM empleados
GROUP BY oficio)
WHERE num_empleados >= 3
ORDER BY num_empleados DESC;
OFICIO NUM_EMPLEADOS
----------------- -------------
DIRECTOR 5
VENDEDOR 4
ADMINISTRATIVO 3
PROGRAMADOR 3
4 filas seleccionadas.
Observa que en la consulta externa podemos usar el alias num_empleados en el WHERE, cosa imposible dentro de la consulta interna (allí habría que usar HAVING COUNT(*) >= 3).
2.3. En SELECT (subconsulta escalar)
SELECT nombre,
salario,
(SELECT ROUND(AVG(salario), 2) FROM empleados) AS media_empresa,
salario - (SELECT ROUND(AVG(salario), 2) FROM empleados) AS diferencia
FROM empleados
WHERE dept_id = 30
ORDER BY salario DESC;
NOMBRE SALARIO MEDIA_EMPRESA DIFERENCIA
--------------------------- ---------- ------------- ----------
Marta 5000 2829,17 2170,83
Pablo 3500 2829,17 670,83
Raquel 2800 2829,17 -29,17
Iván 2600 2829,17 -229,17
Hugo 2500 2829,17 -329,17
Sara 2400 2829,17 -429,17
6 filas seleccionadas.
2.4. En ORDER BY
Departamentos ordenados de más a menos empleados, sin mostrar el número:
SELECT d.nombre AS departamento, d.localidad
FROM departamentos d
ORDER BY (SELECT COUNT(*) FROM empleados e WHERE e.dept_id = d.dept_id) DESC,
d.nombre;
DEPARTAMENTO LOCALIDAD
-------------- ----------
INFORMÁTICA Valencia
VENTAS Madrid
LOGÍSTICA Zaragoza
CONTABILIDAD Sevilla
MARKETING Barcelona
DIRECCIÓN Madrid
INVESTIGACIÓN Bilbao
7 filas seleccionadas.
INFORMÁTICA y VENTAS tienen 6 empleados cada uno; el desempate es alfabético. INVESTIGACIÓN tiene 0 y queda la última.
2.5. En sentencias DML (adelanto del módulo 34)
Subir un 5 % el sueldo de los empleados de LOGÍSTICA, buscando el departamento por su nombre en lugar de escribir su código:
UPDATE empleados
SET salario = salario * 1.05
WHERE dept_id = (SELECT dept_id FROM departamentos WHERE nombre = 'LOGÍSTICA');
SELECT emp_id, nombre, salario FROM empleados WHERE dept_id = 60;
ROLLBACK;
4 filas actualizadas.
EMP_ID NOMBRE SALARIO
---------- --------------------------- ----------
105 Javier 4200
150 Rosa 1680
151 Manuel 1680
152 Cristina 1837,5
4 filas seleccionadas.
Rollback terminado.
El ROLLBACK deshace el cambio para no alterar los datos de prueba (las transacciones se estudian en el módulo 35).
También puedes crear una tabla a partir de una consulta (CTAS, CREATE TABLE ... AS SELECT), que es una subconsulta en una sentencia DDL:
CREATE TABLE resumen_demo AS
SELECT dept_id, COUNT(*) AS num_emp
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id;
SELECT * FROM resumen_demo ORDER BY dept_id;
DROP TABLE resumen_demo PURGE;
Tabla creada.
DEPT_ID NUM_EMP
---------- ----------
10 1
20 6
30 6
40 3
50 3
60 4
6 filas seleccionadas.
Tabla borrada.
2.6. Dónde NO puede aparecer
Una restricción CHECK no admite subconsultas: debe poder evaluarse mirando solo la fila que se inserta.
CREATE TABLE demo_check (
dept_id NUMBER(4),
CONSTRAINT ck_demo_dept CHECK (dept_id IN (SELECT dept_id FROM departamentos))
);
CONSTRAINT ck_demo_dept CHECK (dept_id IN (SELECT dept_id FROM departamentos))
*
ERROR en línea 3:
ORA-02251: subquery not allowed here
Help: https://docs.oracle.com/error-help/db/ora-02251/
Para lo que pretendía este CHECK existe la FOREIGN KEY (módulo 24). Las reglas que de verdad necesitan consultar otras tablas se implementan con disparadores, que pertenecen al Curso de Oracle PL/SQL. Tampoco se admiten subconsultas en el valor DEFAULT de una columna.
3. Subconsultas escalares: una fila, una columna
📘 Concepto: Una subconsulta escalar devuelve exactamente un valor (una fila con una columna). Puede usarse en cualquier sitio donde se usaría un literal o una expresión: a la derecha de
=,>,<…, en una operación aritmética, como columna delSELECT, en unCASE…
Las formas más seguras de garantizar que la subconsulta devuelve una sola fila son:
- Usar una función de agregado sin
GROUP BY(MAX,MIN,AVG,COUNT,SUM): siempre devuelve una fila. - Filtrar por la clave primaria o por una clave alternativa (
WHERE emp_id = 120,WHERE nombre = 'VENTAS', que esUNIQUE).
3.1. El error ORA-01427
¿Qué pasa si la subconsulta devuelve varias filas y la comparamos con =?
SELECT nombre, salario
FROM empleados
WHERE salario = (SELECT salario
FROM empleados
WHERE dept_id = 20);
WHERE salario = (SELECT salario
*
ERROR en línea 3:
ORA-01427: single-row subquery returns more than one row
Help: https://docs.oracle.com/error-help/db/ora-01427/
El departamento 20 tiene seis empleados, así que la subconsulta devuelve seis salarios. El operador = compara un valor con un valor: Oracle no sabe con cuál de los seis comparar y aborta la consulta.
⚠️ Importante: El error se produce al ejecutar, no al analizar la sentencia. Una consulta puede funcionar durante meses y fallar el día en que alguien inserta una segunda fila que cumple la condición. Si la subconsulta no filtra por clave ni usa un agregado, pregúntate siempre: ¿puede devolver más de una fila?
Las soluciones dependen de lo que se quiera expresar:
| Lo que quieres decir | Solución |
|---|---|
| “igual a alguno de ellos” | Cambiar = por IN (sección 5) |
| “mayor que todos” / “mayor que alguno” | > ALL / > ANY, o > (SELECT MAX(...)) / > (SELECT MIN(...)) (sección 6) |
| “el valor de la fila concreta” | Filtrar por la clave primaria |
| “uno cualquiera, el más reciente…” | ORDER BY ... FETCH FIRST 1 ROW ONLY dentro de la subconsulta (ver 3.3) |
Por ejemplo, con IN y el departamento 60:
SELECT nombre, salario
FROM empleados
WHERE salario IN (SELECT salario
FROM empleados
WHERE dept_id = 60);
NOMBRE SALARIO
--------------------------- ----------
Javier 4000
Rosa 1600
Manuel 1600
Cristina 1750
4 filas seleccionadas.
3.2. Subconsulta escalar que no devuelve ninguna fila
Si la subconsulta escalar no encuentra filas, no da error: devuelve NULL. Y comparar con NULL da desconocido, así que la consulta externa no devuelve nada:
SELECT nombre, salario
FROM empleados
WHERE salario = (SELECT salario FROM empleados WHERE emp_id = 999);
ninguna fila seleccionada
En el SELECT el efecto es una columna nula (que SQL*Plus muestra como (nulo) gracias a SET NULL, módulo 27). En esta consulta, DIRECCIÓN no tiene ningún empleado con oficio 'DIRECTOR' (Ana es 'DIRECTOR GENERAL') e INVESTIGACIÓN no tiene empleados, así que ambas columnas salen a NULL:
SELECT d.dept_id,
d.nombre AS departamento,
(SELECT MAX(e.salario) FROM empleados e WHERE e.dept_id = d.dept_id) AS salario_max,
(SELECT e.nombre FROM empleados e WHERE e.dept_id = d.dept_id AND e.oficio = 'DIRECTOR') AS director
FROM departamentos d
ORDER BY d.dept_id;
DEPT_ID DEPARTAMENTO SALARIO_MAX DIRECTOR
---------- -------------- ----------- ------------------------------
10 DIRECCIÓN 6500 (nulo)
20 VENTAS 4800 Luis
30 INFORMÁTICA 5000 Marta
40 CONTABILIDAD 4200 Pedro
50 MARKETING 4300 Laura
60 LOGÍSTICA 4000 Javier
70 INVESTIGACIÓN (nulo) (nulo)
7 filas seleccionadas.
⚠️ Importante: Fíjate en la diferencia:
MAXsobre cero filas devuelve una fila conNULL, mientras que la segunda subconsulta (sin agregado) devuelve cero filas, que en un contexto escalar también se convierte enNULL. Si necesitas un 0 en vez deNULL, envuelve la subconsulta enNVL(...). Ojo:COUNT(*)sobre cero filas devuelve0, noNULL.
3.3. Demasiadas columnas: ORA-00913
Una subconsulta escalar debe tener una sola columna:
SELECT nombre
FROM empleados
WHERE salario = (SELECT salario, comision FROM empleados WHERE emp_id = 110);
WHERE salario = (SELECT salario, comision FROM empleados WHERE emp_id = 110)
*
ERROR en línea 3:
ORA-00913: too many values
Help: https://docs.oracle.com/error-help/db/ora-00913/
Y si lo que quieres es “el último pedido” (una fila concreta entre varias), desde Oracle 12c puedes limitar la subconsulta con FETCH FIRST:
SELECT c.nombre AS cliente,
(SELECT p.pedido_id
FROM pedidos p
WHERE p.cliente_id = c.cliente_id
ORDER BY p.fecha_pedido DESC
FETCH FIRST 1 ROW ONLY) AS ultimo_pedido
FROM clientes c
WHERE c.cliente_id IN (1001, 1002, 1013);
CLIENTE ULTIMO_PEDIDO
--------------------------- -------------
Talleres Martín S.L. 5021
Construcciones Levante S.A. 5018
Carlos Ibáñez Rey (nulo)
3 filas seleccionadas.
Aquí sí se admite ORDER BY dentro de la subconsulta, porque forma parte de la limitación de filas. Carlos Ibáñez (1013) no tiene pedidos y obtiene NULL.
4. Subconsultas de una fila y varias columnas: comparación por pares
📘 Concepto: Una subconsulta de fila devuelve una fila con varias columnas y se compara con una lista de expresiones entre paréntesis (un constructor de fila):
(col1, col2) = (SELECT c1, c2 FROM ...). La comparación es verdadera si todos los pares coinciden a la vez.
Empleados con el mismo oficio y el mismo departamento que Raquel Molina (121):
SELECT emp_id, nombre, oficio, dept_id
FROM empleados
WHERE (oficio, dept_id) = (SELECT oficio, dept_id
FROM empleados
WHERE emp_id = 121)
AND emp_id <> 121;
EMP_ID NOMBRE OFICIO DEPT_ID
---------- --------------------------- ----------------- ----------
122 Iván PROGRAMADOR 30
123 Sara PROGRAMADOR 30
2 filas seleccionadas.
Con constructores de fila Oracle solo admite =, <> (y IN, NOT IN, = ANY…). Los operadores de orden no están definidos para listas:
SELECT nombre
FROM empleados
WHERE (dept_id, salario) > (SELECT dept_id, salario FROM empleados WHERE emp_id = 121);
WHERE (dept_id, salario) > (SELECT dept_id, salario FROM empleados WHERE emp_id = 121)
*
ERROR en línea 3:
ORA-01796: this operator cannot be used with lists
Help: https://docs.oracle.com/error-help/db/ora-01796/
4.1. Pares con varias filas: (a, b) IN (SELECT ...)
La comparación por pares es especialmente útil con IN y una subconsulta agrupada. Empleados que cobran el salario mínimo de su departamento:
SELECT emp_id, nombre, dept_id, salario
FROM empleados
WHERE (dept_id, salario) IN (SELECT dept_id, MIN(salario)
FROM empleados
GROUP BY dept_id)
ORDER BY dept_id, emp_id;
EMP_ID NOMBRE DEPT_ID SALARIO
---------- --------------------------- ---------- ----------
100 Ana 10 6500
114 Lucía 20 1700
123 Sara 30 2400
131 Jorge 40 1650
140 Nuria 50 2300
150 Rosa 60 1600
151 Manuel 60 1600
7 filas seleccionadas.
La subconsulta devuelve estos pares:
SELECT dept_id, MIN(salario) AS minimo
FROM empleados
GROUP BY dept_id
ORDER BY dept_id;
DEPT_ID MINIMO
---------- ----------
10 6500
20 1700
30 2400
40 1650
50 2300
60 1600
(nulo) 900
7 filas seleccionadas.
Rosa y Manuel empatan en el mínimo del 60 y salen los dos. Tomás (160, sin departamento) forma el par (NULL, 900), que no aparece en el resultado: comparar NULL = NULL es desconocido, así que su fila no “encuentra pareja”.
4.2. Por pares frente a “por separado”
Un error muy frecuente es escribir dos condiciones IN independientes creyendo que equivalen a una comparación por pares:
SELECT emp_id, nombre, dept_id, salario
FROM empleados
WHERE dept_id IN (SELECT dept_id FROM empleados GROUP BY dept_id)
AND salario IN (SELECT MIN(salario) FROM empleados GROUP BY dept_id)
ORDER BY dept_id, emp_id;
EMP_ID NOMBRE DEPT_ID SALARIO
---------- --------------------------- ---------- ----------
100 Ana 10 6500
112 Elena 20 2300
114 Lucía 20 1700
123 Sara 30 2400
131 Jorge 40 1650
140 Nuria 50 2300
150 Rosa 60 1600
151 Manuel 60 1600
8 filas seleccionadas.
¡Aparece Elena! Cobra 2300, que es el mínimo de MARKETING, no de VENTAS. La versión “por separado” pregunta “¿su departamento está en la lista y su salario está en la lista?”, sin exigir que ambos valores vengan de la misma fila de la subconsulta. Es la diferencia entre comparar pares y comparar componentes sueltos.
💡 Consejo: Cuando dos condiciones deben cumplirse “a la vez y para el mismo elemento”, usa comparación por pares o una subconsulta correlacionada (sección 8). Dos
INsueltos casi nunca expresan lo que quieres.
5. Subconsultas de varias filas: IN y NOT IN
📘 Concepto:
expr IN (subconsulta)es verdadero siexpres igual a alguno de los valores devueltos.expr NOT IN (subconsulta)es verdadero siexpres distinto de todos ellos. Equivalen a= ANYy<> ALLrespectivamente.
Empleados que son jefes de alguien (su emp_id aparece como jefe_id de otro empleado):
SELECT emp_id, nombre, apellidos, oficio
FROM empleados
WHERE emp_id IN (SELECT jefe_id FROM empleados)
ORDER BY emp_id;
EMP_ID NOMBRE APELLIDOS OFICIO
---------- --------------------------- ------------------ -----------------
100 Ana García López DIRECTOR GENERAL
101 Luis Martínez Ruiz DIRECTOR
102 Marta Sánchez Gil DIRECTOR
103 Pedro Gómez Díaz DIRECTOR
104 Laura Fernández Moreno DIRECTOR
105 Javier López Navarro DIRECTOR
120 Pablo Moreno Herrera ANALISTA
7 filas seleccionadas.
IN elimina duplicados de forma natural: aunque Luis es jefe de cinco personas, aparece una sola vez.
5.1. La trampa de NOT IN con NULL
Lo lógico sería que “empleados que NO son jefes de nadie” fuese simplemente cambiar IN por NOT IN. Compruébalo:
SELECT emp_id, nombre, apellidos, oficio
FROM empleados
WHERE emp_id NOT IN (SELECT jefe_id FROM empleados);
ninguna fila seleccionada
¿Ninguno? Hay 24 empleados y solo 7 son jefes; deberían salir 17. El culpable es Ana García (100), la directora general, que no tiene jefe: su jefe_id es NULL. La subconsulta devuelve esos valores (fíjate en el NULL del final):
SELECT DISTINCT jefe_id FROM empleados ORDER BY jefe_id;
JEFE_ID
----------
100
101
102
103
104
105
120
(nulo)
8 filas seleccionadas.
Ahora razona con la lógica de tres valores (módulo 27). Para el empleado 110:
110 NOT IN (100, 101, 102, 103, 104, 105, 120, NULL)
≡ 110 <> 100 AND 110 <> 101 AND ... AND 110 <> 120 AND 110 <> NULL
≡ TRUE AND TRUE AND ... AND TRUE AND UNKNOWN
≡ UNKNOWN
110 <> NULL es desconocido (no sabemos si el jefe desconocido es el 110 o no), y TRUE AND UNKNOWN es UNKNOWN. El WHERE solo deja pasar las filas TRUE, así que ninguna fila pasa. Basta un único NULL en la subconsulta para que NOT IN no devuelva nunca nada.
Puedes verlo con literales:
SELECT 'aparece' AS resultado FROM dual WHERE 3 NOT IN (1, 2, NULL);
SELECT 'aparece' AS resultado FROM dual WHERE 3 IN (1, 3, NULL);
ninguna fila seleccionada
RESULTA
-------
aparece
1 fila seleccionada.
Con IN el NULL no molesta (... OR 3 = 3 OR 3 = NULL es TRUE porque basta un TRUE en una disyunción); con NOT IN es letal.
Solución 1: excluir los nulos dentro de la subconsulta:
SELECT emp_id, nombre, apellidos, oficio
FROM empleados
WHERE emp_id NOT IN (SELECT jefe_id
FROM empleados
WHERE jefe_id IS NOT NULL)
ORDER BY emp_id;
EMP_ID NOMBRE APELLIDOS OFICIO
---------- --------------------------- ------------------ -----------------
110 Carmen Ruiz Ortega VENDEDOR
111 David Torres Romero VENDEDOR
112 Elena Navarro Castro VENDEDOR
113 Sergio Jiménez Vidal VENDEDOR
114 Lucía Romero Serrano ADMINISTRATIVO
121 Raquel Molina Prieto PROGRAMADOR
122 Iván Castro Méndez PROGRAMADOR
123 Sara Ortiz Delgado PROGRAMADOR
...
17 filas seleccionadas.
Solución 2 (preferible): usar NOT EXISTS, que no tiene este problema (sección 7).
El mismo error aparece con otras columnas que admiten nulos. Los pedidos 5014 y 5023 son pedidos online sin empleado (empleado_id es NULL), así que esta consulta, que pretende listar los empleados que no han gestionado ningún pedido, tampoco devuelve nada:
SELECT emp_id, nombre
FROM empleados
WHERE emp_id NOT IN (SELECT empleado_id FROM pedidos);
ninguna fila seleccionada
En cambio, con una columna NOT NULL en la subconsulta, NOT IN es seguro. pedidos.cliente_id es obligatoria, así que esta consulta funciona como se espera (clientes sin pedidos):
SELECT cliente_id, nombre
FROM clientes
WHERE cliente_id NOT IN (SELECT cliente_id FROM pedidos)
ORDER BY cliente_id;
CLIENTE_ID NOMBRE
---------- ---------------------------
1013 Carlos Ibáñez Rey
1014 Farmacia Central
2 filas seleccionadas.
⚠️ Importante: Regla de oro: no uses
NOT INcon una subconsulta cuya columna admitaNULLsalvo que filtres explícitamenteWHERE col IS NOT NULL. Mejor aún, acostúmbrate a escribirNOT EXISTS. Hoy la columna puede no tener nulos y mañana sí.
6. ANY / SOME y ALL
📘 Concepto:
ANY(sinónimo:SOME) yALLpermiten usar los operadores de comparación (=,<>,<,>,<=,>=) con una subconsulta de varias filas.
expr op ANY (subconsulta): verdadero si la comparación es verdadera para al menos un valor.expr op ALL (subconsulta): verdadero si la comparación es verdadera para todos los valores.
Empleados de otros departamentos que cobran menos que algún empleado de VENTAS:
SELECT nombre, apellidos, salario, dept_id
FROM empleados
WHERE salario < ANY (SELECT salario
FROM empleados
WHERE dept_id = 20)
AND dept_id <> 20
ORDER BY salario DESC;
NOMBRE APELLIDOS SALARIO DEPT_ID
--------------------------- ------------------ ---------- ----------
Laura Fernández Moreno 4300 50
Pedro Gómez Díaz 4200 40
Javier López Navarro 4000 60
Pablo Moreno Herrera 3500 30
Raquel Molina Prieto 2800 30
Álvaro Peña Cano 2700 50
Iván Castro Méndez 2600 30
Isabel Domínguez Vega 2600 40
...
15 filas seleccionadas.
Los salarios de VENTAS son 1700, 1900, 2100, 2200, 2300 y 4800. “Menor que alguno” equivale a “menor que el mayor de ellos” (< 4800), por eso salen todos menos Ana, Marta (5000) y Tomás, que no tiene departamento (NULL <> 20 es desconocido).
Empleados que cobran más que todos los de INFORMÁTICA:
SELECT nombre, apellidos, salario, dept_id
FROM empleados
WHERE salario > ALL (SELECT salario
FROM empleados
WHERE dept_id = 30)
ORDER BY salario DESC;
NOMBRE APELLIDOS SALARIO DEPT_ID
--------------------------- ------------------ ---------- ----------
Ana García López 6500 10
1 fila seleccionada.
“Mayor que todos” es “mayor que el máximo” (5000, el de Marta).
= ANY es exactamente IN. Empleados cuyo oficio existe en MARKETING:
SELECT nombre, oficio
FROM empleados
WHERE oficio = ANY (SELECT oficio FROM empleados WHERE dept_id = 50)
ORDER BY oficio, nombre;
NOMBRE OFICIO
--------------------------- -----------------
Álvaro ANALISTA
Pablo ANALISTA
Javier DIRECTOR
Laura DIRECTOR
Luis DIRECTOR
Marta DIRECTOR
Pedro DIRECTOR
Nuria DISEÑADOR
8 filas seleccionadas.
ANY y ALL también admiten una lista de literales:
SELECT nombre, salario
FROM empleados
WHERE salario > ALL (4000, 4500, 4800);
NOMBRE SALARIO
--------------------------- ----------
Ana 6500
Marta 5000
2 filas seleccionadas.
6.1. Tabla de equivalencias
Con ANY / ALL | Equivale a | Significado |
|---|---|---|
x = ANY (S) | x IN (S) | igual a alguno |
x <> ALL (S) | x NOT IN (S) | distinto de todos |
x > ANY (S) | x > (SELECT MIN(...)) | mayor que el mínimo |
x >= ANY (S) | x >= (SELECT MIN(...)) | mayor o igual que el mínimo |
x < ANY (S) | x < (SELECT MAX(...)) | menor que el máximo |
x <= ANY (S) | x <= (SELECT MAX(...)) | menor o igual que el máximo |
x > ALL (S) | x > (SELECT MAX(...)) | mayor que el máximo |
x >= ALL (S) | x >= (SELECT MAX(...)) | mayor o igual que el máximo |
x < ALL (S) | x < (SELECT MIN(...)) | menor que el mínimo |
x <= ALL (S) | x <= (SELECT MIN(...)) | menor o igual que el mínimo |
x <> ANY (S) | — | distinto de al menos uno (casi siempre verdadero: solo es falso si todos los valores son iguales a x) |
x = ALL (S) | — | igual a todos (solo si todos los valores de S son iguales a x) |
⚠️ Importante: Las equivalencias con
MIN/MAXfallan en dos casos límite: cuando la subconsulta no devuelve filas y cuando contieneNULL. Los vemos a continuación.
6.2. Caso límite 1: la subconsulta está vacía
El departamento 70 no tiene empleados. Mira estas tres consultas:
SELECT COUNT(*) AS filas
FROM empleados
WHERE salario > ALL (SELECT salario FROM empleados WHERE dept_id = 70);
SELECT COUNT(*) AS filas
FROM empleados
WHERE salario > (SELECT MAX(salario) FROM empleados WHERE dept_id = 70);
SELECT COUNT(*) AS filas
FROM empleados
WHERE salario > ANY (SELECT salario FROM empleados WHERE dept_id = 70);
FILAS
----------
24
1 fila seleccionada.
FILAS
----------
0
1 fila seleccionada.
FILAS
----------
0
1 fila seleccionada.
> ALLsobre un conjunto vacío es verdadero para todas las filas: “ser mayor que todos los elementos de un conjunto vacío” se cumple trivialmente (no hay ningún elemento que lo contradiga). Es la verdad vacua de la lógica (∀x ∈ ∅: P(x) es verdadero).> (SELECT MAX(...))sobre cero filas compara conNULLy no devuelve nada.> ANYsobre un conjunto vacío es falso (∃x ∈ ∅: P(x) es falso).
6.3. Caso límite 2: la subconsulta contiene NULL
Las comisiones de VENTAS son:
SELECT comision FROM empleados WHERE dept_id = 20;
COMISION
----------
(nulo)
500
800
300
0
(nulo)
6 filas seleccionadas.
Los dos (nulo) son las comisiones de Luis (101) y Lucía (114). Ahora:
SELECT nombre, salario
FROM empleados
WHERE salario > ALL (SELECT comision FROM empleados WHERE dept_id = 20);
ninguna fila seleccionada
Todo salario es mayor que 800, pero > ALL exige ser mayor que todos, incluido el NULL, y salario > NULL es desconocido. Igual que ocurría con NOT IN (que no es más que <> ALL). En cambio MAX ignora los nulos:
SELECT nombre, salario
FROM empleados
WHERE salario > (SELECT MAX(comision) FROM empleados WHERE dept_id = 20)
AND dept_id = 20;
NOMBRE SALARIO
--------------------------- ----------
Luis 4800
Carmen 2200
David 2100
Elena 2300
Sergio 1900
Lucía 1700
6 filas seleccionadas.
💡 Consejo: En la práctica profesional se usan mucho más
IN,EXISTSy las comparaciones conMAX/MINqueANY/ALL, porque son más legibles. PeroANY/ALLaparecen en exámenes y en código heredado, y debes saber leerlos y conocer sus casos límite.
7. EXISTS y NOT EXISTS
📘 Concepto:
EXISTS (subconsulta)es verdadero si la subconsulta devuelve al menos una fila, y falso si no devuelve ninguna. Nunca es desconocido: solo mira si hay filas, no qué valores contienen.NOT EXISTSes su negación. Casi siempre se usan con subconsultas correlacionadas.
Departamentos que tienen algún empleado:
SELECT d.dept_id, d.nombre AS departamento
FROM departamentos d
WHERE EXISTS (SELECT 1
FROM empleados e
WHERE e.dept_id = d.dept_id)
ORDER BY d.dept_id;
DEPT_ID DEPARTAMENTO
---------- --------------
10 DIRECCIÓN
20 VENTAS
30 INFORMÁTICA
40 CONTABILIDAD
50 MARKETING
60 LOGÍSTICA
6 filas seleccionadas.
Léelo así: “departamentos d para los que existe algún empleado e cuyo departamento es d“. La condición e.dept_id = d.dept_id es la correlación: une la subconsulta con la fila actual de la consulta externa.
Departamentos sin empleados:
SELECT d.dept_id, d.nombre AS departamento
FROM departamentos d
WHERE NOT EXISTS (SELECT 1
FROM empleados e
WHERE e.dept_id = d.dept_id);
DEPT_ID DEPARTAMENTO
---------- --------------
70 INVESTIGACIÓN
1 fila seleccionada.
7.1. NOT EXISTS no cae en la trampa del NULL
Volvamos a “empleados que no han gestionado ningún pedido”, que con NOT IN no devolvía nada:
SELECT e.emp_id, e.nombre, e.oficio
FROM empleados e
WHERE NOT EXISTS (SELECT 1
FROM pedidos p
WHERE p.empleado_id = e.emp_id)
ORDER BY e.emp_id;
EMP_ID NOMBRE OFICIO
---------- --------------------------- -----------------
100 Ana DIRECTOR GENERAL
101 Luis DIRECTOR
102 Marta DIRECTOR
103 Pedro DIRECTOR
104 Laura DIRECTOR
105 Javier DIRECTOR
114 Lucía ADMINISTRATIVO
120 Pablo ANALISTA
...
20 filas seleccionadas.
Solo los cuatro vendedores (110 a 113) han gestionado pedidos. Para cada empleado, la subconsulta busca pedidos con p.empleado_id = e.emp_id; los pedidos con empleado_id nulo simplemente no cumplen la igualdad y no se cuentan, sin “contaminar” el resultado.
Lo mismo con los jefes:
SELECT COUNT(*) AS no_son_jefes
FROM empleados e
WHERE NOT EXISTS (SELECT 1
FROM empleados s
WHERE s.jefe_id = e.emp_id);
NO_SON_JEFES
------------
17
1 fila seleccionada.
7.2. ¿Qué se pone en el SELECT de un EXISTS?
Da igual: EXISTS no mira los valores. Por convención se escribe SELECT 1, SELECT NULL o SELECT *; los tres son igual de eficientes (Oracle no lee ninguna columna para ello).
Clientes con algún pedido pendiente:
SELECT c.cliente_id, c.nombre AS cliente
FROM clientes c
WHERE EXISTS (SELECT NULL
FROM pedidos p
WHERE p.cliente_id = c.cliente_id
AND p.estado = 'PENDIENTE')
ORDER BY c.cliente_id;
CLIENTE_ID CLIENTE
---------- ---------------------------
1008 Gestoría Delta
1009 Laura Benítez Sanz
1012 Diseños Creativos S.L.
3 filas seleccionadas.
Productos que nunca se han vendido:
SELECT p.producto_id, p.nombre AS producto, p.descatalogado
FROM productos p
WHERE NOT EXISTS (SELECT 1
FROM lineas_pedido l
WHERE l.producto_id = p.producto_id);
PRODUCTO_ID PRODUCTO D
----------- ---------------------- -
14 Altavoz Inteligente S
1 fila seleccionada.
7.3. Cuidado con el EXISTS sin correlación
Si olvidas la condición de correlación, el EXISTS es igual para todas las filas: o salen todas o no sale ninguna.
SELECT COUNT(*) AS filas
FROM empleados
WHERE EXISTS (SELECT 1 FROM pedidos WHERE estado = 'CANCELADO');
FILAS
----------
24
1 fila seleccionada.
Como existe algún pedido cancelado, la condición es verdadera para los 24 empleados. Es un error típico: si un EXISTS devuelve “todo o nada”, revisa la correlación.
7.4. Resumen: IN, NOT IN, EXISTS, NOT EXISTS
| Operador | Subconsulta vacía | Subconsulta con NULL | Correlación | Uso recomendado |
|---|---|---|---|---|
IN | Falso | Sin problemas si hay coincidencia | Opcional | Lista de valores a buscar, legible |
NOT IN | Verdadero (todas las filas) | Desconocido para todas → 0 filas | Opcional | Solo con columnas NOT NULL o filtrando IS NOT NULL |
EXISTS | Falso | No le afecta | Casi siempre | Semicombinación (“tiene algún…”) |
NOT EXISTS | Verdadero | No le afecta | Casi siempre | Anticombinación (“no tiene ningún…”), división |
8. Subconsultas correlacionadas
📘 Concepto: Una subconsulta es correlacionada cuando hace referencia a una columna de una tabla de la consulta externa. No puede ejecutarse por sí sola, porque depende de la fila externa que se esté evaluando. Conceptualmente, Oracle la evalúa una vez por cada fila candidata de la consulta externa.
8.1. Cómo se evalúa
Empleados que cobran más que la media de su propio departamento:
SELECT e.nombre, e.apellidos, e.dept_id, e.salario
FROM empleados e
WHERE e.salario > (SELECT AVG(e2.salario)
FROM empleados e2
WHERE e2.dept_id = e.dept_id)
ORDER BY e.dept_id, e.salario DESC;
NOMBRE APELLIDOS DEPT_ID SALARIO
--------------------------- ------------------ ---------- ----------
Luis Martínez Ruiz 20 4800
Marta Sánchez Gil 30 5000
Pablo Moreno Herrera 30 3500
Pedro Gómez Díaz 40 4200
Laura Fernández Moreno 50 4300
Javier López Navarro 60 4000
6 filas seleccionadas.
La tabla empleados aparece dos veces con alias distintos: e es la fila de la consulta externa y e2 recorre la tabla dentro de la subconsulta. El proceso conceptual es:
flowchart TB
A["Tomar la siguiente fila e de EMPLEADOS"] --> B["Sustituir e.dept_id en la subconsulta"]
B --> C["Calcular AVG(e2.salario) de ese departamento"]
C --> D{"¿e.salario > media?"}
D -->|Sí| E["La fila e pasa al resultado"]
D -->|No| F["Se descarta"]
E --> G{"¿Quedan filas?"}
F --> G
G -->|Sí| A
G -->|No| H["Ordenar y devolver"]
Por ejemplo, para algunas filas:
Fila externa e | e.dept_id | Subconsulta con ese valor | Media | ¿e.salario > media? |
|---|---|---|---|---|
| Luis (4800) | 20 | AVG de los 6 de VENTAS | 2500 | Sí |
| Carmen (2200) | 20 | AVG de los 6 de VENTAS | 2500 | No |
| Pablo (3500) | 30 | AVG de los 6 de INFORMÁTICA | 3133,33 | Sí |
| Ana (6500) | 10 | AVG de DIRECCIÓN (solo ella) | 6500 | No (no es mayor) |
| Tomás (900) | NULL | WHERE e2.dept_id = NULL → 0 filas | NULL | Desconocido → no |
Las medias reales de cada departamento son:
SELECT dept_id, ROUND(AVG(salario), 2) AS media
FROM empleados
GROUP BY dept_id
ORDER BY dept_id;
DEPT_ID MEDIA
---------- ----------
10 6500
20 2500
30 3133,33
40 2816,67
50 3100
60 2237,5
(nulo) 900
7 filas seleccionadas.
💡 Consejo: “Una vez por cada fila” es el modelo mental para entender el resultado, no lo que hace físicamente Oracle. El optimizador suele transformar las subconsultas correlacionadas en combinaciones (unnesting) y además guarda en caché los resultados ya calculados para un mismo valor de correlación. Escribe la consulta que exprese mejor la pregunta y deja que el optimizador trabaje; si hay problemas de rendimiento, analiza el plan (sección 12).
8.2. El último pedido de cada cliente
Un patrón muy habitual: “para cada X, la fila con el máximo (o mínimo) de Y”.
SELECT p.cliente_id, p.pedido_id, p.fecha_pedido, p.estado
FROM pedidos p
WHERE p.fecha_pedido = (SELECT MAX(p2.fecha_pedido)
FROM pedidos p2
WHERE p2.cliente_id = p.cliente_id)
ORDER BY p.cliente_id;
CLIENTE_ID PEDIDO_ID FECHA_PEDI ESTADO
---------- ---------- ---------- ------------
1001 5021 15/06/2026 ENVIADO
1002 5018 03/03/2026 ENTREGADO
1003 5015 27/11/2025 ENTREGADO
1004 5012 08/09/2025 ENTREGADO
1005 5019 21/04/2026 ENTREGADO
1006 5011 25/06/2025 ENTREGADO
1007 5020 30/05/2026 ENVIADO
1008 5024 10/07/2026 PENDIENTE
1009 5023 08/07/2026 PENDIENTE
1010 5016 12/01/2026 ENTREGADO
1011 5017 09/02/2026 ENTREGADO
1012 5022 01/07/2026 PENDIENTE
12 filas seleccionadas.
Si un cliente tuviera dos pedidos el mismo día (su fecha máxima), saldrían los dos: la subconsulta correlacionada respeta los empates. El módulo 33 resuelve este mismo problema con funciones analíticas (ROW_NUMBER, RANK), que permiten decidir qué hacer con los empates.
8.3. El producto más caro de cada categoría
SELECT p.categoria_id, p.nombre AS producto, p.precio
FROM productos p
WHERE p.precio = (SELECT MAX(p2.precio)
FROM productos p2
WHERE p2.categoria_id = p.categoria_id)
ORDER BY p.categoria_id;
CATEGORIA_ID PRODUCTO PRECIO
------------ ---------------------- ----------
1 Disco SSD 1TB 89,9
2 Portátil Gaming 16 1499
3 Torre Workstation 1899
4 Impresora Láser 189
5 Teclado mecánico 79,9
6 Monitor 27 4K 329
7 Tablet 10 279
8 Smartphone Gama Alta 999
9 Auriculares Bluetooth 59,9
11 Mesa Elevable 399
10 filas seleccionadas.
La categoría 10 (Oficina) no aparece porque no tiene productos directos (sus productos están en la subcategoría 11, Mobiliario).
8.4. Reglas de visibilidad de nombres
- Una subconsulta “ve” las columnas de sus tablas y las de todas las consultas que la contienen.
- Si un nombre de columna existe en ambas, gana la tabla más interna. Por eso es imprescindible usar alias en las subconsultas correlacionadas:
WHERE dept_id = dept_iddentro de la subconsulta compararíae2.dept_idconsigo mismo (siempre verdadero salvoNULL) y la consulta estaría mal sin dar ningún error. - La consulta externa no ve las columnas de la subconsulta (excepto las de una vista en línea, que se exponen como columnas de la “tabla” derivada).
⚠️ Importante: Un error silencioso clásico: en
SELECT COUNT(*) FROM empleados WHERE emp_id IN (SELECT emp_id FROM proyectos), la tablaproyectosno tiene columnaemp_id, pero Oracle no da error: la resuelve comoempleados.emp_idde la consulta externa (una correlación involuntaria), la condición se convierte en “emp_ides igual a sí mismo” y la consulta devuelve 24, todos los empleados. Califica siempre las columnas con el alias de su tabla:SELECT p.emp_id FROM proyectos psí habría dado el errorORA-00904: invalid identifier.
9. Subconsultas en FROM: vistas en línea y tablas derivadas
📘 Concepto: Una subconsulta en el
FROMse llama vista en línea (inline view) en la terminología de Oracle, y tabla derivada (derived table) en el estándar SQL. Es una tabla virtual que solo existe durante la consulta. Se le puede (y conviene) dar un alias, y sus columnas calculadas deben tener nombre para poder usarlas fuera.
Son la herramienta para calcular en dos pasos: primero se agrega o se transforma, y luego se filtra, se combina o se vuelve a agregar.
9.1. Filtrar un resultado agregado
Pedidos cuyo importe total (con descuento) supera 5000 €:
SELECT t.pedido_id, t.importe
FROM (SELECT l.pedido_id,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS importe
FROM lineas_pedido l
GROUP BY l.pedido_id) t
WHERE t.importe > 5000
ORDER BY t.importe DESC;
PEDIDO_ID IMPORTE
---------- ----------
5010 11657,5
5016 8945,05
5002 7402,5
5004 6232,5
4 filas seleccionadas.
9.2. Agregar un agregado
Importe medio, importe máximo y número de pedidos. No se puede escribir AVG(SUM(...)) junto a otras columnas agregadas normales, pero con una vista en línea es inmediato:
SELECT ROUND(AVG(importe), 2) AS importe_medio,
MAX(importe) AS importe_max,
COUNT(*) AS num_pedidos
FROM (SELECT pedido_id,
SUM(cantidad * precio_unitario * (1 - descuento / 100)) AS importe
FROM lineas_pedido
GROUP BY pedido_id);
IMPORTE_MEDIO IMPORTE_MAX NUM_PEDIDOS
------------- ----------- -----------
2600,64 11657,5 24
1 fila seleccionada.
9.3. Combinar una tabla con un resumen
Empleados de VENTAS con la media de su departamento y su diferencia: la vista en línea m calcula una fila por departamento y la combinamos con empleados:
SELECT e.nombre, e.apellidos, e.salario, m.media, e.salario - m.media AS diferencia
FROM empleados e
JOIN (SELECT dept_id, ROUND(AVG(salario), 2) AS media
FROM empleados
GROUP BY dept_id) m
ON m.dept_id = e.dept_id
WHERE e.dept_id = 20
ORDER BY diferencia DESC;
NOMBRE APELLIDOS SALARIO MEDIA DIFERENCIA
--------------------------- ------------------ ---------- ---------- ----------
Luis Martínez Ruiz 4800 2500 2300
Elena Navarro Castro 2300 2500 -200
Carmen Ruiz Ortega 2200 2500 -300
David Torres Romero 2100 2500 -400
Sergio Jiménez Vidal 1900 2500 -600
Lucía Romero Serrano 1700 2500 -800
6 filas seleccionadas.
Este patrón (tabla JOIN resumen agrupado) suele ser más eficiente que la subconsulta correlacionada equivalente cuando hay muchas filas, porque el resumen se calcula una sola vez.
9.4. Top-N con ROWNUM (estilo clásico, anterior a 12c)
Antes de FETCH FIRST (Oracle 12c, módulo 27), la única forma de obtener “los N primeros” era ordenar en una vista en línea y limitar fuera con la pseudocolumna ROWNUM. Lo verás en muchísimo código existente. Los tres salarios más bajos:
SELECT ROWNUM AS puesto, nombre, salario
FROM (SELECT nombre, salario
FROM empleados
ORDER BY salario)
WHERE ROWNUM <= 3;
PUESTO NOMBRE SALARIO
---------- --------------------------- ----------
1 Tomás 900
2 Rosa 1600
3 Manuel 1600
3 filas seleccionadas.
¿Por qué hace falta la vista en línea? Porque ROWNUM se asigna al leer cada fila, antes del ORDER BY. Sin la subconsulta, Oracle toma las tres primeras filas que encuentra y después las ordena:
SELECT ROWNUM AS puesto, nombre, salario
FROM empleados
WHERE ROWNUM <= 3
ORDER BY salario;
PUESTO NOMBRE SALARIO
---------- --------------------------- ----------
2 Luis 4800
3 Marta 5000
1 Ana 6500
3 filas seleccionadas.
¡Un resultado completamente erróneo! Son simplemente las tres primeras filas almacenadas. Hoy escribe ORDER BY salario FETCH FIRST 3 ROWS ONLY, pero reconoce el patrón clásico cuando lo veas.
9.5. Vistas en línea correlacionadas: LATERAL y CROSS APPLY
Una vista en línea normal no puede hacer referencia a otra tabla del mismo FROM. Desde Oracle 12c existen las vistas en línea laterales, que sí pueden (son como una subconsulta correlacionada que devuelve varias filas y columnas). Se escriben con LATERAL (...) o con la sintaxis CROSS APPLY / OUTER APPLY (esta última conserva las filas sin coincidencias, como un LEFT JOIN). Los dos últimos pedidos de algunos clientes:
SELECT c.cliente_id, c.nombre AS cliente, u.pedido_id, u.fecha_pedido
FROM clientes c
CROSS APPLY (SELECT p.pedido_id, p.fecha_pedido
FROM pedidos p
WHERE p.cliente_id = c.cliente_id
ORDER BY p.fecha_pedido DESC
FETCH FIRST 2 ROWS ONLY) u
WHERE c.cliente_id IN (1001, 1002, 1010)
ORDER BY c.cliente_id, u.fecha_pedido DESC;
CLIENTE_ID CLIENTE PEDIDO_ID FECHA_PEDI
---------- --------------------------- ---------- ----------
1001 Talleres Martín S.L. 5021 15/06/2026
1001 Talleres Martín S.L. 5006 22/03/2025
1002 Construcciones Levante S.A. 5018 03/03/2026
1002 Construcciones Levante S.A. 5009 19/05/2025
1010 Instituto Tecnológico Sur 5016 12/01/2026
1010 Instituto Tecnológico Sur 5010 02/06/2025
6 filas seleccionadas.
El “top-N por grupo” se resuelve de forma más habitual con funciones analíticas (módulo 33), pero CROSS APPLY es una alternativa muy expresiva.
10. Subconsultas escalares correlacionadas en SELECT
Una subconsulta escalar en la lista del SELECT puede estar correlacionada: calcula un valor distinto para cada fila. Es una forma muy legible de añadir datos “resumen” de otras tablas.
SELECT d.dept_id,
d.nombre AS departamento,
(SELECT COUNT(*) FROM empleados e WHERE e.dept_id = d.dept_id) AS num_emp,
(SELECT SUM(e.salario) FROM empleados e WHERE e.dept_id = d.dept_id) AS masa_salarial,
(SELECT COUNT(*) FROM proyectos p WHERE p.dept_id = d.dept_id) AS num_proy
FROM departamentos d
ORDER BY d.dept_id;
DEPT_ID DEPARTAMENTO NUM_EMP MASA_SALARIAL NUM_PROY
---------- -------------- ---------- ------------- ----------
10 DIRECCIÓN 1 6500 1
20 VENTAS 6 15000 0
30 INFORMÁTICA 6 18800 2
40 CONTABILIDAD 3 8450 0
50 MARKETING 3 9300 1
60 LOGÍSTICA 4 8950 1
70 INVESTIGACIÓN 0 (nulo) 0
7 filas seleccionadas.
Ventajas frente a combinar con JOIN y GROUP BY:
- Conserva todas las filas de la tabla externa sin necesidad de
OUTER JOIN(INVESTIGACIÓN sale con 0). - No multiplica filas: combinar
departamentosconempleadosy conproyectosen el mismoJOINduplicaría los empleados por cada proyecto (6 × 2 en INFORMÁTICA) y falsearíaCOUNTySUM. Cada subconsulta escalar agrega su propia tabla por separado.
Para cada cliente, cuántos pedidos tiene y la fecha del último:
SELECT c.cliente_id,
c.nombre AS cliente,
(SELECT COUNT(*) FROM pedidos p WHERE p.cliente_id = c.cliente_id) AS pedidos,
(SELECT MAX(p.fecha_pedido) FROM pedidos p WHERE p.cliente_id = c.cliente_id) AS ultimo
FROM clientes c
ORDER BY pedidos DESC, c.cliente_id;
CLIENTE_ID CLIENTE PEDIDOS ULTIMO
---------- --------------------------- ---------- ----------
1001 Talleres Martín S.L. 3 15/06/2026
1002 Construcciones Levante S.A. 3 03/03/2026
1003 María José Herrera 2 27/11/2025
1004 Academia Nova 2 08/09/2025
1005 Clínica Dental Sonrisa 2 21/04/2026
1007 Hotel Mar Azul S.L. 2 30/05/2026
1008 Gestoría Delta 2 10/07/2026
1009 Laura Benítez Sanz 2 08/07/2026
...
14 filas seleccionadas.
Observa que podemos ordenar por el alias pedidos de la subconsulta escalar.
Un uso clásico más: sustituir una autocombinación por una subconsulta escalar para traer el nombre del jefe:
SELECT e.nombre,
e.salario,
(SELECT j.nombre FROM empleados j WHERE j.emp_id = e.jefe_id) AS jefe
FROM empleados e
WHERE e.dept_id = 30
ORDER BY e.emp_id;
NOMBRE SALARIO JEFE
--------------------------- ---------- ------------------------------
Marta 5000 Ana
Pablo 3500 Marta
Raquel 2800 Pablo
Iván 2600 Pablo
Sara 2400 Pablo
Hugo 2500 Marta
6 filas seleccionadas.
⚠️ Importante: Cada subconsulta escalar en el
SELECTes, conceptualmente, una consulta más por fila. Con tres o cuatro sobre tablas pequeñas no pasa nada, y Oracle dispone de una caché de subconsultas escalares que evita recalcular valores repetidos. Pero si necesitas diez columnas de la misma tabla relacionada, es mejor unLEFT JOINcon una vista en línea agrupada.
11. Subconsultas anidadas en varios niveles
Una subconsulta puede contener otra, y esta otra más. Se evalúan de dentro hacia fuera (si no están correlacionadas).
Clientes que han comprado algún producto de la categoría “Monitores”: cuatro niveles de anidamiento siguiendo las claves ajenas clientes ← pedidos ← lineas_pedido → productos → categorias:
SELECT c.cliente_id, c.nombre AS cliente
FROM clientes c
WHERE c.cliente_id IN (SELECT p.cliente_id
FROM pedidos p
WHERE p.pedido_id IN (SELECT l.pedido_id
FROM lineas_pedido l
WHERE l.producto_id IN (SELECT pr.producto_id
FROM productos pr
WHERE pr.categoria_id = (SELECT ca.categoria_id
FROM categorias ca
WHERE ca.nombre = 'Monitores'))))
ORDER BY c.cliente_id;
CLIENTE_ID CLIENTE
---------- ---------------------------
1001 Talleres Martín S.L.
1002 Construcciones Levante S.A.
1004 Academia Nova
1007 Hotel Mar Azul S.L.
1010 Instituto Tecnológico Sur
1012 Diseños Creativos S.L.
6 filas seleccionadas.
Funciona y no produce duplicados, pero es difícil de leer. Para más de dos o tres niveles es preferible combinar las tablas interiores con JOIN dentro de una sola subconsulta o usar una CTE con WITH (módulo 32).
El segundo salario más alto de la empresa: el máximo de los que son menores que el máximo.
SELECT MAX(salario) AS segundo_salario
FROM empleados
WHERE salario < (SELECT MAX(salario) FROM empleados);
SEGUNDO_SALARIO
---------------
5000
1 fila seleccionada.
Los departamentos con más empleados. Oracle permite anidar funciones de agregado (MAX(COUNT(*))) en una consulta con GROUP BY: primero cuenta por grupo y luego toma el máximo de esos recuentos.
SELECT dept_id, COUNT(*) AS num_emp
FROM empleados
GROUP BY dept_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM empleados
GROUP BY dept_id);
DEPT_ID NUM_EMP
---------- ----------
20 6
30 6
2 filas seleccionadas.
💡 Consejo: Los agregados anidados como
MAX(COUNT(*))son una extensión de Oracle (solo se permite un nivel de anidamiento y requierenGROUP BY). En SQL estándar se escribe con una vista en línea:SELECT MAX(n) FROM (SELECT COUNT(*) AS n FROM empleados GROUP BY dept_id).
12. Subconsultas frente a JOIN
Muchas preguntas pueden responderse con una subconsulta o con un JOIN. No son siempre intercambiables.
12.1. El JOIN puede duplicar filas; IN y EXISTS no
Clientes que han comprado el ratón inalámbrico (producto 6):
SELECT c.cliente_id, c.nombre AS cliente
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 l.producto_id = 6
ORDER BY c.cliente_id;
CLIENTE_ID CLIENTE
---------- ---------------------------
1001 Talleres Martín S.L.
1001 Talleres Martín S.L.
1009 Laura Benítez Sanz
3 filas seleccionadas.
Talleres Martín sale dos veces porque compró ratones en dos pedidos (5001 y 5006). El JOIN devuelve una fila por cada combinación. Con una subconsulta, la pregunta “¿este cliente está entre los que compraron?” se responde una sola vez por cliente:
SELECT c.cliente_id, c.nombre AS cliente
FROM clientes c
WHERE c.cliente_id IN (SELECT p.cliente_id
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE l.producto_id = 6)
ORDER BY c.cliente_id;
CLIENTE_ID CLIENTE
---------- ---------------------------
1001 Talleres Martín S.L.
1009 Laura Benítez Sanz
2 filas seleccionadas.
Esta operación se llama semicombinación (semi-join, ⋉ en álgebra relacional): devuelve las filas de la tabla izquierda que tienen al menos una pareja en la derecha, sin repetirlas y sin columnas de la derecha. Arreglarlo con SELECT DISTINCT sobre el JOIN funciona, pero obliga a ordenar o agrupar todo el resultado y oculta la intención.
12.2. La anticombinación con LEFT JOIN ... IS NULL
El equivalente de NOT EXISTS con combinaciones externas (módulo 30) es el patrón antijoin:
SELECT c.cliente_id, c.nombre AS cliente
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.cliente_id
WHERE p.pedido_id IS NULL;
CLIENTE_ID CLIENTE
---------- ---------------------------
1013 Carlos Ibáñez Rey
1014 Farmacia Central
2 filas seleccionadas.
Funciona porque las filas de clientes sin pedidos se completan con nulos, y p.pedido_id (clave primaria, nunca nula en una fila real) solo es NULL en esas filas añadidas.
12.3. Tabla de equivalencias
| Pregunta | Subconsulta | JOIN equivalente |
|---|---|---|
| Filas de A con alguna pareja en B | WHERE a.k IN (SELECT b.k FROM b) o WHERE EXISTS (... b.k = a.k) | SELECT DISTINCT a.* FROM a JOIN b ON b.k = a.k |
| Filas de A sin pareja en B | WHERE NOT EXISTS (... b.k = a.k) | FROM a LEFT JOIN b ON b.k = a.k WHERE b.k IS NULL |
| Datos de A y de B juntos | Subconsulta escalar en el SELECT (un valor) | JOIN (cualquier número de columnas) |
| Comparar con un agregado | WHERE x > (SELECT AVG(...)) | JOIN con una vista en línea agregada |
12.4. ¿Y el rendimiento?
El optimizador de Oracle transforma las subconsultas en combinaciones cuando puede (subquery unnesting). Puedes comprobarlo con EXPLAIN PLAN y la función DBMS_XPLAN.DISPLAY (se usa desde SQL; no es necesario programar nada):
EXPLAIN PLAN FOR
SELECT d.nombre
FROM departamentos d
WHERE NOT EXISTS (SELECT 1 FROM empleados e WHERE e.dept_id = d.dept_id);
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC'));
Explicado.
PLAN_TABLE_OUTPUT
------------------------------------------------------------
Plan hash value: 3497725142
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN ANTI | |
| 2 | TABLE ACCESS FULL| DEPARTAMENTOS |
| 3 | TABLE ACCESS FULL| EMPLEADOS |
--------------------------------------------
10 filas seleccionadas.
El NOT EXISTS se ha ejecutado como una anticombinación hash (HASH JOIN ANTI): no se ha recorrido empleados una vez por departamento. Con IN el plan es una semicombinación:
EXPLAIN PLAN FOR
SELECT d.nombre
FROM departamentos d
WHERE d.dept_id IN (SELECT e.dept_id FROM empleados e);
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC'));
Explicado.
PLAN_TABLE_OUTPUT
------------------------------------------------------------
Plan hash value: 911596735
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN SEMI | |
| 2 | TABLE ACCESS FULL| DEPARTAMENTOS |
| 3 | TABLE ACCESS FULL| EMPLEADOS |
--------------------------------------------
10 filas seleccionadas.
Y el NOT IN sobre una columna que admite nulos utiliza una variante especial, HASH JOIN ANTI NA (null-aware), que implementa precisamente la semántica de los nulos vista en la sección 5.1:
EXPLAIN PLAN FOR
SELECT e.emp_id
FROM empleados e
WHERE e.emp_id NOT IN (SELECT p.empleado_id FROM pedidos p);
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC'));
Explicado.
PLAN_TABLE_OUTPUT
------------------------------------------------------------
Plan hash value: 180185432
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN ANTI NA | |
| 2 | INDEX FAST FULL SCAN| PK_EMPLEADOS |
| 3 | TABLE ACCESS FULL | PEDIDOS |
----------------------------------------------
10 filas seleccionadas.
Conclusiones prácticas:
| Situación | Recomendación |
|---|---|
| Necesitas columnas de ambas tablas | JOIN |
| Solo quieres filas de A que “tienen” algo en B | EXISTS o IN (sin duplicados, intención clara) |
| Filas de A que “no tienen” nada en B | NOT EXISTS (seguro con nulos) |
| Comparar con un valor agregado | Subconsulta escalar o vista en línea agrupada |
| Muchas columnas resumen de otra tabla | LEFT JOIN con vista en línea agrupada |
| Duda de rendimiento | Escribe la versión más clara y mira el plan; el optimizador suele igualarlas |
💡 Consejo: La vieja regla “
EXISTSes más rápido queIN” (o al revés) venía de versiones muy antiguas de Oracle. Hoy ambas se transforman en el mismo plan en la inmensa mayoría de los casos. Lo que sí importa es la semántica conNULL(NOT INfrente aNOT EXISTS) y los duplicados (JOINfrente a semicombinación).
13. La división relacional en SQL
📘 Concepto: La división (
÷, módulo 18) responde a preguntas con “todos”: “clientes que han comprado todos los productos de una categoría”, “empleados asignados a todos los proyectos de su departamento”. SiR(a, b)yS(b),R ÷ Sdevuelve los valores deaque aparecen enRemparejados con todos los valores deS. SQL no tiene un operador de división, así que se expresa con doble negación.
La lógica es la de la doble negación: “el cliente ha comprado todos los productos” equivale a “no existe ningún producto que el cliente no haya comprado”. En lógica: ∀p (compró(c, p)) ≡ ¬∃p (¬compró(c, p)). En SQL, cada ¬∃ es un NOT EXISTS:
SELECT ... FROM A a
WHERE NOT EXISTS ( -- no existe ningún elemento s del divisor...
SELECT 1 FROM S s
WHERE NOT EXISTS ( -- ...que a no tenga
SELECT 1 FROM R r
WHERE r.a = a.a AND r.b = s.b));
13.1. Clientes que han comprado productos de todas las categorías raíz
Las categorías raíz son Informática (1), Electrónica (7) y Oficina (10). Cada producto pertenece a una categoría que puede estar en el nivel 1, 2 o 3 de la jerarquía, así que primero necesitamos saber a qué raíz pertenece cada categoría. Como la jerarquía tiene como máximo tres niveles, basta con dos autocombinaciones externas (el módulo 33 lo resuelve para cualquier profundidad con CONNECT BY):
SELECT c.categoria_id,
c.nombre AS categoria,
COALESCE(a.categoria_id, p.categoria_id, c.categoria_id) AS raiz_id
FROM categorias c
LEFT JOIN categorias p ON p.categoria_id = c.categoria_padre_id
LEFT JOIN categorias a ON a.categoria_id = p.categoria_padre_id
ORDER BY c.categoria_id;
CATEGORIA_ID CATEGORIA RAIZ_ID
------------ ---------------------- ----------
1 Informática 1
2 Portátiles 1
3 Ordenadores sobremesa 1
4 Periféricos 1
5 Ratones y teclados 1
6 Monitores 1
7 Electrónica 7
8 Telefonía 7
9 Audio 7
10 Oficina 10
11 Mobiliario 10
11 filas seleccionadas.
(p es el padre y a el abuelo; la raíz es el antepasado más lejano que exista.) Ahora la división: “clientes cl para los que no existe ninguna categoría raíz r de la que no hayan comprado nada”:
SELECT cl.cliente_id, cl.nombre AS cliente
FROM clientes cl
WHERE NOT EXISTS (
SELECT 1
FROM categorias r
WHERE r.categoria_padre_id IS NULL
AND NOT EXISTS (
SELECT 1
FROM pedidos pe
JOIN lineas_pedido l ON l.pedido_id = pe.pedido_id
JOIN productos pr ON pr.producto_id = l.producto_id
JOIN (SELECT c.categoria_id,
COALESCE(a.categoria_id, p.categoria_id, c.categoria_id) AS raiz_id
FROM categorias c
LEFT JOIN categorias p ON p.categoria_id = c.categoria_padre_id
LEFT JOIN categorias a ON a.categoria_id = p.categoria_padre_id) cr
ON cr.categoria_id = pr.categoria_id
WHERE pe.cliente_id = cl.cliente_id
AND cr.raiz_id = r.categoria_id));
ninguna fila seleccionada
Ningún cliente ha comprado de las tres familias. Comprobémoslo contando cuántas raíces distintas ha comprado cada uno:
SELECT pe.cliente_id, COUNT(DISTINCT cr.raiz_id) AS raices_compradas
FROM pedidos pe
JOIN lineas_pedido l ON l.pedido_id = pe.pedido_id
JOIN productos pr ON pr.producto_id = l.producto_id
JOIN (SELECT c.categoria_id,
COALESCE(a.categoria_id, p.categoria_id, c.categoria_id) AS raiz_id
FROM categorias c
LEFT JOIN categorias p ON p.categoria_id = c.categoria_padre_id
LEFT JOIN categorias a ON a.categoria_id = p.categoria_padre_id) cr
ON cr.categoria_id = pr.categoria_id
GROUP BY pe.cliente_id
ORDER BY raices_compradas DESC, pe.cliente_id;
CLIENTE_ID RAICES_COMPRADAS
---------- ----------------
1002 2
1003 2
1005 2
1007 2
1008 2
1009 2
1012 2
1001 1
1004 1
1006 1
1010 1
1011 1
12 filas seleccionadas.
Efectivamente, el máximo es 2. Construcciones Levante (1002) ha comprado Informática y Electrónica, pero ningún mueble. Simulemos que compra una silla ergonómica (producto 15, de Mobiliario → Oficina) y repitamos la división, deshaciendo luego el cambio:
INSERT INTO pedidos (pedido_id, cliente_id, empleado_id, fecha_pedido, estado)
VALUES (9999, 1002, 110, DATE '2026-09-15', 'PENDIENTE');
INSERT INTO lineas_pedido (pedido_id, num_linea, producto_id, cantidad, precio_unitario)
VALUES (9999, 1, 15, 1, 249);
-- La misma consulta de división de antes
SELECT cl.cliente_id, cl.nombre AS cliente
FROM clientes cl
WHERE NOT EXISTS (
SELECT 1
FROM categorias r
WHERE r.categoria_padre_id IS NULL
AND NOT EXISTS (
SELECT 1
FROM pedidos pe
JOIN lineas_pedido l ON l.pedido_id = pe.pedido_id
JOIN productos pr ON pr.producto_id = l.producto_id
JOIN (SELECT c.categoria_id,
COALESCE(a.categoria_id, p.categoria_id, c.categoria_id) AS raiz_id
FROM categorias c
LEFT JOIN categorias p ON p.categoria_id = c.categoria_padre_id
LEFT JOIN categorias a ON a.categoria_id = p.categoria_padre_id) cr
ON cr.categoria_id = pr.categoria_id
WHERE pe.cliente_id = cl.cliente_id
AND cr.raiz_id = r.categoria_id));
ROLLBACK;
1 fila creada.
1 fila creada.
CLIENTE_ID CLIENTE
---------- ---------------------------
1002 Construcciones Levante S.A.
1 fila seleccionada.
Rollback terminado.
Ahora sí: para 1002 no existe ninguna raíz sin compras.
13.2. Empleados asignados a todos los proyectos de su departamento
Primero, un caso concreto: empleados de INFORMÁTICA (30) asignados a todos los proyectos del departamento 30 (los proyectos 1 y 2):
SELECT e.emp_id, e.nombre, e.apellidos
FROM empleados e
WHERE e.dept_id = 30
AND NOT EXISTS (SELECT 1
FROM proyectos p
WHERE p.dept_id = e.dept_id
AND NOT EXISTS (SELECT 1
FROM asignaciones a
WHERE a.emp_id = e.emp_id
AND a.proyecto_id = p.proyecto_id));
EMP_ID NOMBRE APELLIDOS
---------- --------------------------- ------------------
120 Pablo Moreno Herrera
1 fila seleccionada.
Pablo es el único que está en “Migración a la nube” y en “Nueva tienda online”. Generalicemos a todos los empleados quitando el filtro dept_id = 30:
SELECT e.emp_id, e.nombre, e.dept_id
FROM empleados e
WHERE NOT EXISTS (SELECT 1
FROM proyectos p
WHERE p.dept_id = e.dept_id
AND NOT EXISTS (SELECT 1
FROM asignaciones a
WHERE a.emp_id = e.emp_id
AND a.proyecto_id = p.proyecto_id))
ORDER BY e.dept_id, e.emp_id;
EMP_ID NOMBRE DEPT_ID
---------- --------------------------- ----------
101 Luis 20
110 Carmen 20
111 David 20
112 Elena 20
113 Sergio 20
114 Lucía 20
120 Pablo 30
103 Pedro 40
130 Isabel 40
131 Jorge 40
104 Laura 50
140 Nuria 50
141 Álvaro 50
105 Javier 60
150 Rosa 60
152 Cristina 60
160 Tomás (nulo)
17 filas seleccionadas.
¡Sorpresa! Aparecen todos los empleados de VENTAS y CONTABILIDAD, que no tienen ningún proyecto, y Tomás, que no tiene departamento. Es otra vez la verdad vacua: si el divisor está vacío (“los proyectos de mi departamento” no existen), entonces “no existe ningún proyecto de mi departamento en el que no esté” es verdadero. Matemáticamente es correcto (R ÷ ∅ devuelve todo), pero rara vez es lo que se quiere. Se exige además que el divisor no esté vacío:
SELECT e.emp_id, e.nombre, e.dept_id
FROM empleados e
WHERE EXISTS (SELECT 1 FROM proyectos p WHERE p.dept_id = e.dept_id)
AND NOT EXISTS (SELECT 1
FROM proyectos p
WHERE p.dept_id = e.dept_id
AND NOT EXISTS (SELECT 1
FROM asignaciones a
WHERE a.emp_id = e.emp_id
AND a.proyecto_id = p.proyecto_id))
ORDER BY e.dept_id, e.emp_id;
EMP_ID NOMBRE DEPT_ID
---------- --------------------------- ----------
120 Pablo 30
104 Laura 50
140 Nuria 50
141 Álvaro 50
105 Javier 60
150 Rosa 60
152 Cristina 60
7 filas seleccionadas.
DIRECCIÓN tiene un proyecto (el 5, sin asignaciones), así que Ana no aparece. Manuel (151) no está en el proyecto 4 de LOGÍSTICA.
13.3. División por conteo
Una alternativa muy usada es contar: un empleado está en todos los proyectos de su departamento si el número de proyectos de su departamento en los que está es igual al número total de proyectos de su departamento.
SELECT a.emp_id, e.nombre, e.dept_id
FROM asignaciones a
JOIN proyectos p ON p.proyecto_id = a.proyecto_id
JOIN empleados e ON e.emp_id = a.emp_id
WHERE p.dept_id = e.dept_id
GROUP BY a.emp_id, e.nombre, e.dept_id
HAVING COUNT(*) = (SELECT COUNT(*)
FROM proyectos p2
WHERE p2.dept_id = e.dept_id)
ORDER BY e.dept_id, a.emp_id;
EMP_ID NOMBRE DEPT_ID
---------- --------------------------- ----------
120 Pablo 30
104 Laura 50
140 Nuria 50
141 Álvaro 50
105 Javier 60
150 Rosa 60
152 Cristina 60
7 filas seleccionadas.
| Técnica | Ventajas | Inconvenientes |
|---|---|---|
Doble NOT EXISTS | Traducción directa de la lógica; no le afectan los duplicados | Más difícil de leer; cuidado con el divisor vacío |
Conteo con HAVING COUNT | Fácil de entender; permite variantes (“al menos 3 de los 5”) | Hay que asegurarse de contar solo elementos del divisor y sin duplicados (COUNT(DISTINCT ...)) |
💡 Consejo: En la versión por conteo, el
WHERE p.dept_id = e.dept_ides esencial: sin él contaríamos también asignaciones a proyectos de otros departamentos (Nuria está en el proyecto 2, de INFORMÁTICA) y el conteo dejaría de ser comparable. Si en la tabla dividendo pudiera haber parejas repetidas, usaCOUNT(DISTINCT ...).
14. Errores frecuentes y buenas prácticas
| Error | Síntoma | Solución |
|---|---|---|
Usar = con una subconsulta que devuelve varias filas | ORA-01427 (a veces solo con ciertos datos) | IN, ANY/ALL, agregado o filtro por clave |
| Subconsulta escalar con dos columnas | ORA-00913: too many values | Una sola columna, o comparación por pares |
ORDER BY dentro de una subconsulta de WHERE | ORA-00907: missing right parenthesis | Quitarlo (o usar FETCH FIRST si se quiere limitar) |
NOT IN con nulos en la subconsulta | Cero filas sin error | NOT EXISTS o WHERE col IS NOT NULL |
> ALL con subconsulta vacía | Devuelve todas las filas | Comprobar con EXISTS o usar MAX |
Olvidar la correlación en EXISTS | Todas las filas o ninguna | Añadir la condición que une con la consulta externa |
| Columnas sin alias en subconsultas correlacionadas | Correlación involuntaria, resultado erróneo sin error | Calificar siempre con alias (e., e2.) |
Dos IN sueltos en lugar de un par | Filas que mezclan valores de distintas filas | (a, b) IN (SELECT ...) |
JOIN para “tiene algún…” | Filas duplicadas | EXISTS / IN |
| División con divisor vacío | Aparecen elementos que no tienen nada | Añadir EXISTS sobre el divisor |
Usar ROWNUM y ORDER BY en el mismo nivel | Top-N incorrecto | Ordenar en una vista en línea o usar FETCH FIRST |
Buenas prácticas:
- Prueba la subconsulta sola antes de incrustarla (si no está correlacionada). Es la forma más rápida de detectar errores.
- Sangra cada nivel de anidamiento y alinea los paréntesis.
- Usa alias significativos y distintos en cada nivel (
e,e2,jpara jefe…). - Prefiere
EXISTS/NOT EXISTSpara semicombinaciones y anticombinaciones;INpara listas de valores pequeñas y legibles. - Si una subconsulta se repite o tiene más de dos niveles, piensa en
WITH(módulo 32).
15. Ejercicios
Todos los ejercicios usan el esquema EMPRESA (empresa_crear.sql, empresa_datos.sql). Intenta resolverlos antes de abrir la solución; muchos admiten varias soluciones correctas.
Ejercicios resueltos
Ejercicio 1: Más que Elena
Muestra nombre, apellidos y salario de los empleados que cobran más que Elena Navarro Castro, buscándola por nombre y apellidos. Ordena por salario descendente.
Ver solución
SELECT nombre, apellidos, salario
FROM empleados
WHERE salario > (SELECT salario
FROM empleados
WHERE nombre = 'Elena' AND apellidos = 'Navarro Castro')
ORDER BY salario DESC, nombre;
NOMBRE APELLIDOS SALARIO
--------------------------- ------------------ ----------
Ana García López 6500
Marta Sánchez Gil 5000
Luis Martínez Ruiz 4800
Laura Fernández Moreno 4300
Pedro Gómez Díaz 4200
Javier López Navarro 4000
Pablo Moreno Herrera 3500
Raquel Molina Prieto 2800
...
13 filas seleccionadas.
Elena cobra 2300. Como nombre y apellidos no son clave, si hubiera dos “Elena Navarro Castro” obtendríamos ORA-01427; en un caso real conviene filtrar por emp_id o por email (que es UNIQUE).
Ejercicio 2: Compañeros de Hugo
Lista los empleados que trabajan en el mismo departamento que Hugo Rubio (124), sin incluirle a él.
Ver solución
SELECT emp_id, nombre, apellidos, oficio
FROM empleados
WHERE dept_id = (SELECT dept_id FROM empleados WHERE emp_id = 124)
AND emp_id <> 124
ORDER BY emp_id;
EMP_ID NOMBRE APELLIDOS OFICIO
---------- --------------------------- ------------------ -----------------
102 Marta Sánchez Gil DIRECTOR
120 Pablo Moreno Herrera ANALISTA
121 Raquel Molina Prieto PROGRAMADOR
122 Iván Castro Méndez PROGRAMADOR
123 Sara Ortiz Delgado PROGRAMADOR
5 filas seleccionadas.
La subconsulta filtra por clave primaria, así que es seguro usar =.
Ejercicio 3: El producto más barato
Muestra el producto (o productos, si hay empate) de precio mínimo.
Ver solución
SELECT producto_id, nombre AS producto, precio
FROM productos
WHERE precio = (SELECT MIN(precio) FROM productos);
PRODUCTO_ID PRODUCTO PRECIO
----------- ---------------------- ----------
6 Ratón inalámbrico 19,9
1 fila seleccionada.
A diferencia de ORDER BY precio FETCH FIRST 1 ROW ONLY, esta versión devuelve todos los empatados.
Ejercicio 4: Presupuestos por encima de la media
Muestra los departamentos cuyo presupuesto supera la media de los presupuestos. ¿Cuántos departamentos intervienen en esa media?
Ver solución
SELECT d.nombre AS departamento, d.presupuesto
FROM departamentos d
WHERE d.presupuesto > (SELECT AVG(presupuesto) FROM departamentos)
ORDER BY d.presupuesto DESC;
SELECT AVG(presupuesto) AS media, COUNT(presupuesto) AS con_presupuesto, COUNT(*) AS total
FROM departamentos;
DEPARTAMENTO PRESUPUESTO
-------------- -----------
DIRECCIÓN 500000
INFORMÁTICA 420000
VENTAS 350000
3 filas seleccionadas.
MEDIA CON_PRESUPUESTO TOTAL
---------- --------------- ----------
300000 6 7
1 fila seleccionada.
AVG ignora el presupuesto nulo de INVESTIGACIÓN: la media es 1 800 000 / 6 = 300 000, no 1 800 000 / 7. Si quisieras tratar el nulo como 0, usarías AVG(NVL(presupuesto, 0)).
Ejercicio 5: Mismo oficio que Lucía
Lista los empleados con el mismo oficio que Lucía Romero (114), excluyéndola.
Ver solución
SELECT emp_id, nombre, apellidos, dept_id
FROM empleados
WHERE oficio = (SELECT oficio FROM empleados WHERE emp_id = 114)
AND emp_id <> 114
ORDER BY emp_id;
EMP_ID NOMBRE APELLIDOS DEPT_ID
---------- --------------------------- ------------------ ----------
131 Jorge Vázquez Ramos 40
152 Cristina Medina Blanco 60
2 filas seleccionadas.
Ejercicio 6: Oficios peor pagados que los programadores
Muestra los oficios cuyo salario medio es inferior al salario medio de los programadores.
Ver solución
SELECT oficio, ROUND(AVG(salario), 2) AS media
FROM empleados
GROUP BY oficio
HAVING AVG(salario) < (SELECT AVG(salario)
FROM empleados
WHERE oficio = 'PROGRAMADOR')
ORDER BY media;
OFICIO MEDIA
----------------- ----------
BECARIO 900
OPERARIO 1600
ADMINISTRATIVO 1700
VENDEDOR 2125
DISEÑADOR 2300
TÉCNICO SISTEMAS 2500
6 filas seleccionadas.
La media de los programadores es (2800 + 2600 + 2400) / 3 = 2600. La subconsulta va en el HAVING porque compara un agregado por grupo.
Ejercicio 7: Clientes con pedidos en curso
Lista los clientes que tienen algún pedido en estado PENDIENTE o ENVIADO, con su ciudad, usando IN.
Ver solución
SELECT cliente_id, nombre AS cliente, ciudad
FROM clientes
WHERE cliente_id IN (SELECT cliente_id
FROM pedidos
WHERE estado IN ('PENDIENTE', 'ENVIADO'))
ORDER BY cliente_id;
CLIENTE_ID CLIENTE CIUDAD
---------- --------------------------- -----------
1001 Talleres Martín S.L. Valencia
1007 Hotel Mar Azul S.L. Palma
1008 Gestoría Delta Zaragoza
1009 Laura Benítez Sanz Bilbao
1012 Diseños Creativos S.L. Barcelona
5 filas seleccionadas.
Ejercicio 8: Productos sin ventas con NOT IN
Lista los productos que no aparecen en ninguna línea de pedido usando NOT IN. Justifica por qué aquí NOT IN es seguro.
Ver solución
SELECT producto_id, nombre AS producto, stock
FROM productos
WHERE producto_id NOT IN (SELECT producto_id FROM lineas_pedido);
PRODUCTO_ID PRODUCTO STOCK
----------- ---------------------- ----------
14 Altavoz Inteligente 0
1 fila seleccionada.
Es seguro porque lineas_pedido.producto_id tiene la restricción nn_lin_producto NOT NULL: la subconsulta nunca puede devolver NULL.
Ejercicio 9: Los comerciales
Muestra los empleados que son comerciales de algún cliente, ordenados por salario descendente.
Ver solución
SELECT nombre, apellidos, salario
FROM empleados
WHERE emp_id IN (SELECT comercial_id FROM clientes)
ORDER BY salario DESC;
NOMBRE APELLIDOS SALARIO
--------------------------- ------------------ ----------
Elena Navarro Castro 2300
Carmen Ruiz Ortega 2200
David Torres Romero 2100
Sergio Jiménez Vidal 1900
4 filas seleccionadas.
comercial_id tiene nulos (clientes 1009 y 1013), pero con IN no importa. Si preguntáramos por los empleados que no son comerciales con NOT IN, obtendríamos cero filas.
Ejercicio 10: Empleados de VENTAS sin pedidos
Lista los empleados del departamento VENTAS (búscalo por nombre) que no han gestionado ningún pedido. Después prueba la versión con NOT IN y explica el resultado.
Ver solución
SELECT e.emp_id, e.nombre, e.apellidos, e.oficio
FROM empleados e
WHERE e.dept_id = (SELECT dept_id FROM departamentos WHERE nombre = 'VENTAS')
AND NOT EXISTS (SELECT 1
FROM pedidos p
WHERE p.empleado_id = e.emp_id)
ORDER BY e.emp_id;
EMP_ID NOMBRE APELLIDOS OFICIO
---------- --------------------------- ------------------ -----------------
101 Luis Martínez Ruiz DIRECTOR
114 Lucía Romero Serrano ADMINISTRATIVO
2 filas seleccionadas.
La versión con NOT IN:
SELECT e.emp_id, e.nombre
FROM empleados e
WHERE e.dept_id = 20
AND e.emp_id NOT IN (SELECT p.empleado_id FROM pedidos p);
ninguna fila seleccionada
Los pedidos 5014 y 5023 tienen empleado_id nulo, así que NOT IN es desconocido para todos. Añadiendo WHERE p.empleado_id IS NOT NULL a la subconsulta se obtienen otra vez Luis y Lucía.
Ejercicio 11: Mejor pagados que todo VENTAS
Lista los empleados que cobran más que todos los empleados de VENTAS usando ALL. Escribe una versión equivalente sin ALL.
Ver solución
SELECT nombre, apellidos, salario, dept_id
FROM empleados
WHERE salario > ALL (SELECT salario FROM empleados WHERE dept_id = 20)
ORDER BY salario DESC;
NOMBRE APELLIDOS SALARIO DEPT_ID
--------------------------- ------------------ ---------- ----------
Ana García López 6500 10
Marta Sánchez Gil 5000 30
2 filas seleccionadas.
Equivalente: WHERE salario > (SELECT MAX(salario) FROM empleados WHERE dept_id = 20). Ambas coinciden porque el departamento 20 tiene empleados y salario es NOT NULL.
Ejercicio 12: Más caros que algún portátil
Lista los productos que no son portátiles pero cuestan más que algún portátil (busca la categoría por nombre).
Ver solución
SELECT nombre AS producto, precio
FROM productos
WHERE precio > ANY (SELECT precio
FROM productos
WHERE categoria_id = (SELECT categoria_id
FROM categorias
WHERE nombre = 'Portátiles'))
AND categoria_id <> 2
ORDER BY precio DESC;
PRODUCTO PRECIO
---------------------- ----------
Torre Workstation 1899
Smartphone Gama Alta 999
Torre Oficina i5 649
3 filas seleccionadas.
“Mayor que alguno” equivale a “mayor que el más barato” (499 €). Para ser rigurosos, la condición categoria_id <> 2 debería usar también la subconsulta en lugar del código 2.
Ejercicio 13: Departamentos con comisionistas
Muestra los departamentos que tienen al menos un empleado con comisión mayor que cero, usando EXISTS.
Ver solución
SELECT d.dept_id, d.nombre AS departamento
FROM departamentos d
WHERE EXISTS (SELECT 1
FROM empleados e
WHERE e.dept_id = d.dept_id
AND e.comision > 0);
DEPT_ID DEPARTAMENTO
---------- --------------
20 VENTAS
1 fila seleccionada.
Sergio tiene comisión 0 y Luis y Lucía NULL; para los dos últimos NULL > 0 es desconocido, pero basta con que Carmen, David o Elena cumplan la condición.
Ejercicio 14: Clientes de comerciales bien pagados
Lista los clientes cuyo comercial cobra más de 2100 €, indicando el código del comercial.
Ver solución
SELECT c.nombre AS cliente, c.comercial_id
FROM clientes c
WHERE c.comercial_id IN (SELECT e.emp_id
FROM empleados e
WHERE e.salario > 2100)
ORDER BY c.comercial_id, c.nombre;
CLIENTE COMERCIAL_ID
--------------------------- ------------
Construcciones Levante S.A. 110
Diseños Creativos S.L. 110
Talleres Martín S.L. 110
Antonio Ruiz Pérez 112
Clínica Dental Sonrisa 112
Instituto Tecnológico Sur 112
6 filas seleccionadas.
La subconsulta devuelve también directores, analistas, etc. No importa: solo coinciden los que realmente son comerciales (110, Carmen, 2200 € y 112, Elena, 2300 €).
Ejercicio 15: Pedidos con monitores
Muestra el número de los pedidos que incluyen algún producto de la categoría “Monitores”.
Ver solución
SELECT DISTINCT l.pedido_id
FROM lineas_pedido l
WHERE l.producto_id IN (SELECT p.producto_id
FROM productos p
JOIN categorias c ON c.categoria_id = p.categoria_id
WHERE c.nombre = 'Monitores')
ORDER BY l.pedido_id;
PEDIDO_ID
----------
5001
5002
5004
5009
5010
5013
5020
5021
8 filas seleccionadas.
El DISTINCT es necesario porque la consulta externa recorre líneas: si un pedido tuviera dos líneas con monitores (por ejemplo, uno de 24 y otro de 27 pulgadas), su número saldría dos veces. En los datos actuales no ocurre, pero la consulta debe ser correcta para cualquier contenido. Una alternativa sin DISTINCT es recorrer pedidos con WHERE EXISTS (...).
Ejercicio 16: El mejor pagado de cada oficio
Para cada oficio, muestra el empleado (o empleados) con el salario más alto de ese oficio.
Ver solución
SELECT e.oficio, e.nombre, e.apellidos, e.salario
FROM empleados e
WHERE e.salario = (SELECT MAX(e2.salario)
FROM empleados e2
WHERE e2.oficio = e.oficio)
ORDER BY e.oficio;
OFICIO NOMBRE APELLIDOS SALARIO
----------------- --------------------------- ------------------ ----------
ADMINISTRATIVO Cristina Medina Blanco 1750
ANALISTA Pablo Moreno Herrera 3500
BECARIO Tomás Reyes Fuentes 900
CONTABLE Isabel Domínguez Vega 2600
DIRECTOR Marta Sánchez Gil 5000
DIRECTOR GENERAL Ana García López 6500
DISEÑADOR Nuria Gil Soto 2300
OPERARIO Rosa Iglesias Cruz 1600
OPERARIO Manuel Herrero Lozano 1600
PROGRAMADOR Raquel Molina Prieto 2800
TÉCNICO SISTEMAS Hugo Rubio Marín 2500
VENDEDOR Elena Navarro Castro 2300
12 filas seleccionadas.
Subconsulta correlacionada por oficio. Los dos operarios empatan y salen ambos. Con comparación por pares sería: WHERE (oficio, salario) IN (SELECT oficio, MAX(salario) FROM empleados GROUP BY oficio).
Ejercicio 17: El veterano de cada departamento
Muestra el empleado con la fecha de alta más antigua de cada departamento.
Ver solución
SELECT e.dept_id, e.nombre, e.apellidos, e.fecha_alta
FROM empleados e
WHERE e.fecha_alta = (SELECT MIN(e2.fecha_alta)
FROM empleados e2
WHERE e2.dept_id = e.dept_id)
ORDER BY e.dept_id;
DEPT_ID NOMBRE APELLIDOS FECHA_ALTA
---------- --------------------------- ------------------ ----------
10 Ana García López 15/01/2010
20 Luis Martínez Ruiz 01/03/2012
30 Marta Sánchez Gil 20/06/2011
40 Pedro Gómez Díaz 10/09/2013
50 Laura Fernández Moreno 02/02/2015
60 Javier López Navarro 11/11/2014
6 filas seleccionadas.
En todos los casos el más antiguo es el director. Tomás (sin departamento) no sale: la correlación e2.dept_id = NULL no encuentra filas.
Ejercicio 18: Clientes por encima de la media de su comercial
Lista los clientes cuyo límite de crédito supera la media de los límites de los clientes de su mismo comercial.
Ver solución
SELECT c.nombre AS cliente, c.comercial_id, c.limite_credito
FROM clientes c
WHERE c.limite_credito > (SELECT AVG(c2.limite_credito)
FROM clientes c2
WHERE c2.comercial_id = c.comercial_id)
ORDER BY c.comercial_id, c.limite_credito DESC;
CLIENTE COMERCIAL_ID LIMITE_CREDITO
--------------------------- ------------ --------------
Construcciones Levante S.A. 110 25000
Hotel Mar Azul S.L. 111 15000
Instituto Tecnológico Sur 112 20000
Gestoría Delta 113 5000
Farmacia Central 113 4000
5 filas seleccionadas.
Los clientes sin comercial (1009, 1013) no se comparan con nadie: su subconsulta devuelve NULL.
Ejercicio 19: Cerca del sueldo del jefe
Muestra los empleados que cobran más del 60 % del salario de su jefe, con el salario del jefe y el porcentaje, usando subconsultas escalares (sin JOIN).
Ver solución
SELECT e.nombre, e.apellidos, e.salario,
(SELECT j.salario FROM empleados j WHERE j.emp_id = e.jefe_id) AS salario_jefe,
ROUND(100 * e.salario / (SELECT j.salario FROM empleados j WHERE j.emp_id = e.jefe_id), 1) AS pct
FROM empleados e
WHERE e.salario > 0.6 * (SELECT j.salario FROM empleados j WHERE j.emp_id = e.jefe_id)
ORDER BY pct DESC;
NOMBRE APELLIDOS SALARIO SALARIO_JEFE PCT
--------------------------- ------------------ ---------- ------------ ----------
Raquel Molina Prieto 2800 3500 80
Marta Sánchez Gil 5000 6500 76,9
Iván Castro Méndez 2600 3500 74,3
Luis Martínez Ruiz 4800 6500 73,8
Pablo Moreno Herrera 3500 5000 70
Sara Ortiz Delgado 2400 3500 68,6
Laura Fernández Moreno 4300 6500 66,2
Pedro Gómez Díaz 4200 6500 64,6
Álvaro Peña Cano 2700 4300 62,8
Isabel Domínguez Vega 2600 4200 61,9
Javier López Navarro 4000 6500 61,5
11 filas seleccionadas.
Funciona, pero repite tres veces la misma subconsulta. Una autocombinación (JOIN empleados j ON j.emp_id = e.jefe_id, módulo 30) es aquí más clara. Ningún empleado cobra más que su jefe en EMPRESA.
Ejercicio 20: Ficha de departamentos
Para cada departamento muestra su nombre, el número de empleados, el salario máximo (0 si no tiene empleados) y el número de proyectos abiertos (sin fecha de fin). Ordena por número de empleados descendente.
Ver solución
SELECT d.nombre AS departamento,
(SELECT COUNT(*) FROM empleados e WHERE e.dept_id = d.dept_id) AS num_emp,
NVL((SELECT MAX(e.salario) FROM empleados e WHERE e.dept_id = d.dept_id), 0) AS salario_max,
(SELECT COUNT(*) FROM proyectos p WHERE p.dept_id = d.dept_id
AND p.fecha_fin IS NULL) AS proy_abiertos
FROM departamentos d
ORDER BY num_emp DESC, d.nombre;
DEPARTAMENTO NUM_EMP SALARIO_MAX PROY_ABIERTOS
-------------- ---------- ----------- -------------
INFORMÁTICA 6 5000 1
VENTAS 6 4800 0
LOGÍSTICA 4 4000 1
CONTABILIDAD 3 4200 0
MARKETING 3 4300 0
DIRECCIÓN 1 6500 1
INVESTIGACIÓN 0 0 0
7 filas seleccionadas.
NVL convierte en 0 el NULL que devuelve MAX sobre cero filas; COUNT ya devuelve 0.
Ejercicio 21: Unidades vendidas de periféricos de entrada y monitores
Para los productos de las categorías 5 y 6, muestra nombre, precio y unidades vendidas en total (0 si no se ha vendido), con una subconsulta escalar.
Ver solución
SELECT p.nombre AS producto,
p.precio,
(SELECT NVL(SUM(l.cantidad), 0)
FROM lineas_pedido l
WHERE l.producto_id = p.producto_id) AS unidades_vendidas
FROM productos p
WHERE p.categoria_id IN (5, 6)
ORDER BY unidades_vendidas DESC;
PRODUCTO PRECIO UNIDADES_VENDIDAS
---------------------- ---------- -----------------
Monitor 24 FHD 139 40
Pack teclado y ratón 34,5 30
Teclado mecánico 79,9 15
Monitor 27 4K 329 13
Ratón inalámbrico 19,9 7
5 filas seleccionadas.
Ejercicio 22: Pedidos por encima del importe medio
Muestra los pedidos cuyo importe (con descuentos) supera el importe medio de todos los pedidos. Usa una vista en línea para los importes y un agregado anidado para la media.
Ver solución
SELECT t.pedido_id, t.importe
FROM (SELECT pedido_id,
ROUND(SUM(cantidad * precio_unitario * (1 - descuento / 100)), 2) AS importe
FROM lineas_pedido
GROUP BY pedido_id) t
WHERE t.importe > (SELECT AVG(SUM(cantidad * precio_unitario * (1 - descuento / 100)))
FROM lineas_pedido
GROUP BY pedido_id)
ORDER BY t.importe DESC;
PEDIDO_ID IMPORTE
---------- ----------
5010 11657,5
5016 8945,05
5002 7402,5
5004 6232,5
5009 4772,44
5 filas seleccionadas.
La media es 2600,64 €. Observa que el cálculo del importe se repite: en el módulo 32 verás cómo escribirlo una sola vez con WITH.
Ejercicio 23: Categorías con más productos
Muestra la categoría o categorías con más productos.
Ver solución
SELECT p.categoria_id, c.nombre AS categoria, COUNT(*) AS num_productos
FROM productos p
JOIN categorias c ON c.categoria_id = p.categoria_id
GROUP BY p.categoria_id, c.nombre
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM productos
GROUP BY categoria_id);
CATEGORIA_ID CATEGORIA NUM_PRODUCTOS
------------ ---------------------- -------------
2 Portátiles 3
5 Ratones y teclados 3
2 filas seleccionadas.
Ejercicio 24: El tercer salario
Muestra el empleado (o empleados) con el tercer salario más alto, usando solo subconsultas anidadas con MAX.
Ver solución
SELECT nombre, apellidos, salario
FROM empleados
WHERE salario = (SELECT MAX(salario)
FROM empleados
WHERE salario < (SELECT MAX(salario)
FROM empleados
WHERE salario < (SELECT MAX(salario) FROM empleados)));
NOMBRE APELLIDOS SALARIO
--------------------------- ------------------ ----------
Luis Martínez Ruiz 4800
1 fila seleccionada.
De dentro hacia fuera: 6500 (el máximo), 5000 (el máximo menor que 6500) y 4800 (el máximo menor que 5000). Para el “N-ésimo” genérico se usan funciones analíticas como DENSE_RANK (módulo 33).
Ejercicio 25: La última incorporación de cada departamento
Muestra el empleado más reciente de cada departamento usando comparación por pares.
Ver solución
SELECT e.dept_id, e.nombre, e.apellidos, e.fecha_alta
FROM empleados e
WHERE (e.dept_id, e.fecha_alta) IN (SELECT dept_id, MAX(fecha_alta)
FROM empleados
GROUP BY dept_id)
ORDER BY e.dept_id;
DEPT_ID NOMBRE APELLIDOS FECHA_ALTA
---------- --------------------------- ------------------ ----------
10 Ana García López 15/01/2010
20 Sergio Jiménez Vidal 01/09/2021
30 Sara Ortiz Delgado 01/06/2022
40 Jorge Vázquez Ramos 09/01/2023
50 Álvaro Peña Cano 12/04/2021
60 Manuel Herrero Lozano 17/10/2022
6 filas seleccionadas.
Tomás, el más reciente de todos, no aparece: su par (NULL, 01/09/2025) no puede coincidir con nada.
Ejercicio 26: Monitor 4K y portátil gaming
Lista los clientes que han comprado tanto el “Monitor 27 4K” como el “Portátil Gaming 16” (no necesariamente en el mismo pedido).
Ver solución
SELECT c.cliente_id, c.nombre AS cliente
FROM clientes c
WHERE c.cliente_id IN (SELECT p.cliente_id
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE l.producto_id = (SELECT producto_id FROM productos
WHERE nombre = 'Monitor 27 4K'))
AND c.cliente_id IN (SELECT p.cliente_id
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE l.producto_id = (SELECT producto_id FROM productos
WHERE nombre = 'Portátil Gaming 16'))
ORDER BY c.cliente_id;
CLIENTE_ID CLIENTE
---------- ---------------------------
1001 Talleres Martín S.L.
1004 Academia Nova
1012 Diseños Creativos S.L.
3 filas seleccionadas.
Un error típico es escribir WHERE l.producto_id = 10 AND l.producto_id = 2 en una sola subconsulta: una misma línea no puede tener dos productos. Hacen falta dos condiciones independientes (o INTERSECT, módulo 32).
Ejercicio 27: Categorías sin productos propios
Muestra las categorías que no tienen ningún producto asignado directamente.
Ver solución
SELECT c.categoria_id, c.nombre AS categoria
FROM categorias c
WHERE NOT EXISTS (SELECT 1 FROM productos p WHERE p.categoria_id = c.categoria_id);
CATEGORIA_ID CATEGORIA
------------ ----------------------
10 Oficina
1 fila seleccionada.
Ejercicio 28: Proyectos con colaboradores externos
Muestra los proyectos en los que participa algún empleado de un departamento distinto al del proyecto (o sin departamento).
Ver solución
SELECT pr.proyecto_id, pr.nombre AS proyecto
FROM proyectos pr
WHERE EXISTS (SELECT 1
FROM asignaciones a
JOIN empleados e ON e.emp_id = a.emp_id
WHERE a.proyecto_id = pr.proyecto_id
AND (e.dept_id <> pr.dept_id OR e.dept_id IS NULL));
PROYECTO_ID PROYECTO
----------- ------------------------
2 Nueva tienda online
3 Campaña Navidad 2025
2 filas seleccionadas.
En el 2 (INFORMÁTICA) participa Nuria, de MARKETING; en el 3 (MARKETING), Carmen, de VENTAS. El OR e.dept_id IS NULL evita perder a un empleado sin departamento, cuya comparación <> sería desconocida.
Ejercicio 29: Más horas que la media
Muestra los empleados cuyas horas totales en proyectos superan la media de horas por asignación, con sus horas totales.
Ver solución
SELECT e.nombre, e.apellidos, e.dept_id,
(SELECT SUM(a.horas) FROM asignaciones a WHERE a.emp_id = e.emp_id) AS horas_totales
FROM empleados e
WHERE (SELECT SUM(a.horas) FROM asignaciones a WHERE a.emp_id = e.emp_id)
> (SELECT AVG(horas) FROM asignaciones)
ORDER BY horas_totales DESC;
SELECT ROUND(AVG(horas), 2) AS media_horas FROM asignaciones;
NOMBRE APELLIDOS DEPT_ID HORAS_TOTALES
--------------------------- ------------------ ---------- -------------
Pablo Moreno Herrera 30 750
Raquel Molina Prieto 30 600
Iván Castro Méndez 30 520
Sara Ortiz Delgado 30 410
Hugo Rubio Marín 30 380
Nuria Gil Soto 50 320
6 filas seleccionadas.
MEDIA_HORAS
-----------
254,29
1 fila seleccionada.
Las subconsultas escalares pueden aparecer a ambos lados de la comparación. Los empleados sin asignaciones tienen SUM nulo y no pasan el filtro.
Ejercicio 30: Clientes que superan su límite de crédito
Muestra los clientes cuya facturación total (pedidos no cancelados, con descuentos) supera su límite de crédito. Después lista los que superan el 80 % del límite, con el porcentaje.
Ver solución
SELECT c.cliente_id, c.nombre AS cliente, c.limite_credito, f.facturado
FROM clientes c
JOIN (SELECT p.cliente_id,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS facturado
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
GROUP BY p.cliente_id) f
ON f.cliente_id = c.cliente_id
WHERE f.facturado > c.limite_credito;
CLIENTE_ID CLIENTE LIMITE_CREDITO FACTURADO
---------- --------------------------- -------------- ----------
1010 Instituto Tecnológico Sur 20000 20602,55
1 fila seleccionada.
Cambiando el filtro por WHERE f.facturado > 0.8 * c.limite_credito y añadiendo ROUND(100 * f.facturado / c.limite_credito, 1) AS pct_limite:
CLIENTE_ID LIMITE_CREDITO FACTURADO PCT_LIMITE
---------- -------------- ---------- ----------
1010 20000 20602,55 103
1004 8000 7891,3 98,6
2 filas seleccionadas.
La vista en línea f calcula la facturación una vez por cliente y se combina con clientes para comparar con el límite de cada uno.
Ejercicio 31: División: todos los monitores
Lista los clientes que han comprado todos los productos de la categoría “Monitores”, con doble NOT EXISTS.
Ver solución
SELECT c.cliente_id, c.nombre AS cliente
FROM clientes c
WHERE NOT EXISTS (
SELECT 1
FROM productos pr
WHERE pr.categoria_id = (SELECT categoria_id FROM categorias WHERE nombre = 'Monitores')
AND NOT EXISTS (
SELECT 1
FROM pedidos pe
JOIN lineas_pedido l ON l.pedido_id = pe.pedido_id
WHERE pe.cliente_id = c.cliente_id
AND l.producto_id = pr.producto_id));
CLIENTE_ID CLIENTE
---------- ---------------------------
1001 Talleres Martín S.L.
1002 Construcciones Levante S.A.
2 filas seleccionadas.
“No existe ningún monitor que el cliente no haya comprado.” Solo 1001 y 1002 han comprado el 24 FHD y el 27 4K.
Ejercicio 32: División por conteo
Resuelve el ejercicio anterior contando (con GROUP BY y HAVING).
Ver solución
SELECT pe.cliente_id
FROM pedidos pe
JOIN lineas_pedido l ON l.pedido_id = pe.pedido_id
JOIN productos pr ON pr.producto_id = l.producto_id
WHERE pr.categoria_id = 6
GROUP BY pe.cliente_id
HAVING COUNT(DISTINCT l.producto_id) = (SELECT COUNT(*) FROM productos WHERE categoria_id = 6)
ORDER BY pe.cliente_id;
CLIENTE_ID
----------
1001
1002
2 filas seleccionadas.
El DISTINCT es imprescindible: un cliente que hubiera comprado dos veces el mismo monitor y nunca el otro tendría COUNT(*) = 2 y se colaría en el resultado.
Ejercicio 33: Departamentos sin sueldos bajos
Lista los departamentos en los que todos los empleados cobran más de 2000 €. Analiza qué pasa con el departamento sin empleados y corrígelo.
Ver solución
SELECT d.dept_id, d.nombre AS departamento
FROM departamentos d
WHERE NOT EXISTS (SELECT 1
FROM empleados e
WHERE e.dept_id = d.dept_id
AND e.salario <= 2000)
ORDER BY d.dept_id;
DEPT_ID DEPARTAMENTO
---------- --------------
10 DIRECCIÓN
30 INFORMÁTICA
50 MARKETING
70 INVESTIGACIÓN
4 filas seleccionadas.
INVESTIGACIÓN aparece por verdad vacua (no tiene ningún empleado que cobre 2000 o menos… porque no tiene empleados). Versión con ALL y exigiendo que haya empleados:
SELECT d.dept_id, d.nombre AS departamento
FROM departamentos d
WHERE 2000 < ALL (SELECT e.salario FROM empleados e WHERE e.dept_id = d.dept_id)
AND EXISTS (SELECT 1 FROM empleados e WHERE e.dept_id = d.dept_id)
ORDER BY d.dept_id;
DEPT_ID DEPARTAMENTO
---------- --------------
10 DIRECCIÓN
30 INFORMÁTICA
50 MARKETING
3 filas seleccionadas.
Ejercicio 34: Clientes con todo entregado
Lista los clientes que tienen al menos un pedido y todos sus pedidos están en estado ENTREGADO.
Ver solución
SELECT c.cliente_id, c.nombre AS cliente
FROM clientes c
WHERE EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.cliente_id)
AND NOT EXISTS (SELECT 1
FROM pedidos p
WHERE p.cliente_id = c.cliente_id
AND p.estado <> 'ENTREGADO')
ORDER BY c.cliente_id;
CLIENTE_ID CLIENTE
---------- ---------------------------
1002 Construcciones Levante S.A.
1003 María José Herrera
1004 Academia Nova
1005 Clínica Dental Sonrisa
1006 Antonio Ruiz Pérez
1010 Instituto Tecnológico Sur
1011 Panadería La Espiga
7 filas seleccionadas.
“Todos entregados” = “no existe ninguno no entregado”. Sin el EXISTS, saldrían también 1013 y 1014, que no tienen pedidos.
Ejercicio 35: Proyectos con todo su departamento
Muestra los proyectos en los que participan todos los empleados del departamento al que pertenece el proyecto (división en el otro sentido).
Ver solución
SELECT pr.proyecto_id, pr.nombre AS proyecto
FROM proyectos pr
WHERE EXISTS (SELECT 1 FROM empleados e WHERE e.dept_id = pr.dept_id)
AND NOT EXISTS (SELECT 1
FROM empleados e
WHERE e.dept_id = pr.dept_id
AND NOT EXISTS (SELECT 1
FROM asignaciones a
WHERE a.emp_id = e.emp_id
AND a.proyecto_id = pr.proyecto_id));
PROYECTO_ID PROYECTO
----------- ------------------------
3 Campaña Navidad 2025
1 fila seleccionada.
En la “Campaña Navidad 2025” están Laura, Nuria y Álvaro, los tres empleados de MARKETING (además de Carmen, de fuera). El proyecto 4 no cumple porque falta Manuel.
Ejercicio 36: Subida a los que están por debajo de su media (DML)
Escribe un UPDATE que suba un 5 % el salario de los empleados que cobran menos que la media de su departamento. Comprueba cuántas filas modifica y cómo queda LOGÍSTICA, y deshaz el cambio.
Ver solución
UPDATE empleados e
SET e.salario = ROUND(e.salario * 1.05, 2)
WHERE e.salario < (SELECT AVG(e2.salario)
FROM empleados e2
WHERE e2.dept_id = e.dept_id);
SELECT emp_id, nombre, dept_id, salario
FROM empleados
WHERE dept_id = 60
ORDER BY emp_id;
ROLLBACK;
16 filas actualizadas.
EMP_ID NOMBRE DEPT_ID SALARIO
---------- --------------------------- ---------- ----------
105 Javier 60 4000
150 Rosa 60 1680
151 Manuel 60 1680
152 Cristina 60 1837,5
4 filas seleccionadas.
Rollback terminado.
La subconsulta correlacionada se evalúa con los valores anteriores a la actualización (Oracle garantiza una lectura consistente durante toda la sentencia), así que subir a Rosa no cambia la media con la que se compara a Manuel. Tomás no se actualiza: su media es NULL.
Ejercicio 37: El equipo del proyecto más caro
Muestra los empleados asignados al proyecto de mayor presupuesto, con tres niveles de subconsultas.
Ver solución
SELECT e.nombre, e.apellidos, e.dept_id
FROM empleados e
WHERE e.emp_id IN (SELECT a.emp_id
FROM asignaciones a
WHERE a.proyecto_id IN (SELECT pr.proyecto_id
FROM proyectos pr
WHERE pr.presupuesto = (SELECT MAX(presupuesto)
FROM proyectos)))
ORDER BY e.nombre;
NOMBRE APELLIDOS DEPT_ID
--------------------------- ------------------ ----------
Hugo Rubio Marín 30
Pablo Moreno Herrera 30
Raquel Molina Prieto 30
3 filas seleccionadas.
El proyecto de mayor presupuesto es “Migración a la nube” (120 000 €). Se usa IN en el segundo nivel por si hubiera empate en el máximo.
Ejercicios propuestos
Ejercicio 38: Productos más caros que la media de su categoría
Lista los productos cuyo precio supera el precio medio de los productos de su misma categoría.
Ejercicio 39: Clientes de la misma ciudad que un pedido pendiente
Muestra los clientes que viven en alguna ciudad donde hay al menos un cliente con un pedido PENDIENTE (sin incluir a los propios clientes con pedidos pendientes).
Ejercicio 40: Empleados que no son comerciales
Lista los empleados de VENTAS que no son comerciales de ningún cliente. Resuélvelo con NOT IN (correctamente) y con NOT EXISTS.
Ejercicio 41: El pedido más grande de cada cliente
Para cada cliente con pedidos, muestra su pedido de mayor importe y ese importe.
Ejercicio 42: Productos de pedidos de Madrid
Lista los productos que se han vendido a algún cliente de la provincia de Madrid, sin duplicados y sin usar DISTINCT.
Ejercicio 43: Departamentos más caros que la media
Muestra los departamentos cuya masa salarial (suma de salarios) supera la masa salarial media por departamento.
Ejercicio 44: Comparación por pares con proyectos
Lista las asignaciones (emp_id, proyecto_id) en las que el empleado tiene el mayor número de horas de ese proyecto.
Ejercicio 45: ANY y ALL sobre comisiones
Lista los empleados cuyo salario es menor que todas las comisiones de VENTAS distintas de nulo multiplicadas por 3. Explica qué ocurre si no filtras los nulos.
Ejercicio 46: Clientes con productos de todas las subcategorías de Periféricos
Lista los clientes que han comprado al menos un producto de cada una de las categorías hijas de “Periféricos” (Ratones y teclados, Monitores).
Ejercicio 47: Empleados que trabajan en todos los proyectos de Marketing
Muestra los empleados (de cualquier departamento) asignados a todos los proyectos del departamento MARKETING.
Ejercicio 48: Ficha de cliente
Para cada cliente, muestra el nombre de su comercial, el número de pedidos, el importe total facturado y la fecha del primer pedido, usando subconsultas escalares (los clientes sin comercial o sin pedidos deben aparecer).
Ejercicio 49: Borrado con subconsulta
Escribe (y deshaz con ROLLBACK) un DELETE que elimine las líneas de los pedidos cancelados. Comprueba antes con un SELECT cuántas filas se borrarán.
Resumen
| Concepto | Sintaxis / idea clave |
|---|---|
| Subconsulta | SELECT entre paréntesis dentro de otra sentencia |
| Escalar | Una fila y una columna: WHERE x = (SELECT MAX(...) ...); vacía → NULL |
ORA-01427 | La subconsulta escalar devolvió varias filas: usa IN, agregado o clave |
ORA-00913 | Demasiadas columnas en la subconsulta |
| De fila / por pares | (a, b) = (SELECT c1, c2 ...), (a, b) IN (SELECT c1, c2 ... GROUP BY ...) |
IN / NOT IN | Igual a alguno / distinto de todos; NOT IN con un NULL → 0 filas |
ANY / SOME | Verdadero si se cumple para alguno; vacío → falso |
ALL | Verdadero si se cumple para todos; vacío → verdadero |
EXISTS / NOT EXISTS | ¿Devuelve alguna fila? Nunca desconocido; inmune a nulos |
| Correlacionada | Usa columnas externas; se evalúa (conceptualmente) por cada fila externa |
| Vista en línea | Subconsulta en FROM con alias: FROM (SELECT ...) t |
Escalar en SELECT | Una columna calculada por fila: (SELECT COUNT(*) ... WHERE x.k = t.k) |
LATERAL / CROSS APPLY | Vista en línea que puede referenciar tablas anteriores del FROM (12c+) |
| Agregados anidados | MAX(COUNT(*)) con GROUP BY (extensión de Oracle) |
| Semicombinación | IN / EXISTS: sin duplicados (⋉) |
| Anticombinación | NOT EXISTS o LEFT JOIN ... WHERE b.k IS NULL |
| División | Doble NOT EXISTS o HAVING COUNT(DISTINCT ...) = (SELECT COUNT(*) ...); cuidado con el divisor vacío |
| Verdad vacua | “Para todo elemento de un conjunto vacío” es siempre verdadero |