Subconsultas y consultas avanzadas
Una subconsulta es una consulta completa encerrada dentro de otra consulta. Permite filtrar datos en función de resultados calculados dinámicamente, crear columnas con valores derivados y construir tablas temporales sobre la marcha. Los operadores IN y EXISTS son los mecanismos principales para trabajar con subconsultas, y su elección afecta directamente al rendimiento.
Resumen rápido
Una subconsulta es una consulta completa encerrada dentro de otra consulta. Permite filtrar datos en función de resultados calculados dinámicamente, crear columnas con valores derivados y construir tablas temporales sobre la marcha. Los operadores IN y EXISTS son los mecanismos principales para trabajar con subconsultas, y su elección afecta directamente al rendimiento.
Conceptos clave
Consulta SQL escrita entre paréntesis dentro de otra consulta. Se ejecuta primero y su resultado se usa por la consulta exterior para filtrar, calcular o construir datos. Permite responder preguntas compuestas del tipo "dame los países cuyas ciudades tienen más de X habitantes".
Subconsulta que devuelve exactamente un valor: una fila y una columna. Se puede usar en SELECT para crear columnas calculadas dinámicamente o en WHERE para comparar con un umbral calculado (como el precio máximo o el promedio global).
Subconsulta que devuelve múltiples filas de una sola columna. Se usa con IN, ANY o ALL para verificar si un valor pertenece o supera alguno de los valores de esa lista. Ejemplo: los códigos de país de las ciudades con más de 5 millones de habitantes.
Subconsulta que referencia una columna de la consulta exterior. Se ejecuta una vez por cada fila que procesa la consulta exterior, lo que la hace más flexible pero también más costosa si la tabla tiene muchos registros. Se usa típicamente con EXISTS.
Operador que verifica si un valor coincide con alguno de los resultados devueltos por la subconsulta. Recorre toda la lista antes de devolver el resultado. Más eficiente cuando la subconsulta devuelve pocos valores y los datos son relativamente estáticos.
Operador que verifica si la subconsulta devuelve al menos una fila. Se detiene en el primer resultado encontrado sin procesar el resto. Más eficiente que IN en tablas grandes y en subconsultas correlacionadas, porque no necesita recorrer toda la lista.
Negación de EXISTS: devuelve verdadero si la subconsulta no encuentra ninguna fila que cumpla la condición. Se usa para identificar registros que no tienen correspondencia en otra tabla, como idiomas que solo se hablan en un único país.
Subconsulta colocada dentro de la lista de columnas del SELECT. Genera una columna calculada dinámicamente para cada fila del resultado. Debe ser escalar (devolver exactamente un valor por fila). Útil para porcentajes, referencias cruzadas o comparaciones fila a fila.
Subconsulta que actúa como tabla temporal: el resultado de la subconsulta se trata como si fuera una tabla real a la que se puede añadir un alias y consultarla desde el exterior. Permite pre-filtrar o pre-agregar datos antes de la consulta principal.
Función que redondea un número a un número especificado de decimales. ROUND(valor, 2) redondea a dos decimales. Se usa habitualmente para formatear porcentajes o resultados de divisiones.
Contenido de la clase
Qué es una subconsulta y cuándo es necesaria
Una subconsulta es una pregunta dentro de otra pregunta. Cuando la respuesta que necesitas depende de un dato que primero hay que calcular, una subconsulta permite resolver ambas partes en una sola consulta.
El caso más simple: obtener el producto con el precio más alto. No se puede filtrar directamente por "el precio máximo" porque ese valor no se conoce de antemano. La subconsulta lo calcula primero, y la consulta exterior filtra usando ese resultado.
Los tres tipos de resultado que puede devolver una subconsulta
No todas las subconsultas devuelven el mismo tipo de dato. El tipo de resultado determina dónde se puede colocar la subconsulta y qué operador hay que usar para trabajar con ella.
| Tipo | Qué devuelve | Operador habitual | Ejemplo de uso |
|---|---|---|---|
| Escalar | Un único valor (1 fila, 1 columna) | =, >, < | precio > (SELECT MAX(precio) FROM ...) |
| Lista | Múltiples filas, una columna | IN, ANY, ALL | code IN (SELECT countrycode FROM city WHERE ...) |
| Correlacionada | Depende de la fila exterior, puede devolver tabla completa | EXISTS, NOT EXISTS | WHERE NOT EXISTS (SELECT 1 FROM ... WHERE ...) |
La distinción más importante es entre las subconsultas independientes (escalar y lista) y las correlacionadas. Las independientes se ejecutan una sola vez. Las correlacionadas se ejecutan una vez por cada fila que procesa la consulta exterior.
IN: comprobar pertenencia a una lista
IN verifica si el valor de una columna aparece en alguno de los resultados devueltos por la subconsulta. La subconsulta se ejecuta primero y genera la lista; después, la consulta exterior compara cada fila contra esa lista.
Es la opción natural cuando la subconsulta devuelve múltiples valores y se quiere comprobar pertenencia: "dame los países cuyo código está en la lista de países que tienen ciudades con más de 5 millones de habitantes".
- IN recorre toda la lista antes de devolver el resultado. Si la lista es muy larga, puede ser lento.
- NOT IN hace lo contrario: devuelve las filas cuyo valor no aparece en la lista. Cuidado: si la lista contiene algún NULL, NOT IN devuelve vacío, porque NULL = NULL no es verdadero en SQL.
- DISTINCT dentro de la subconsulta elimina duplicados de la lista antes de comparar, lo que puede mejorar el rendimiento.
EXISTS y NOT EXISTS: verificar existencia
EXISTS no comprueba qué devuelve la subconsulta, sino si devuelve algo. En cuanto encuentra una fila que cumple la condición, se detiene. No necesita recorrer el resto. Por eso es más eficiente que IN cuando la tabla es grande.
NOT EXISTS es la herramienta perfecta para encontrar exclusividad: registros que no tienen correspondencia en otra tabla. Identificar idiomas hablados en un único país, clientes sin ningún pedido, o películas que nunca han sido alquiladas son casos típicos de NOT EXISTS.
| IN | EXISTS | |
|---|---|---|
| Qué verifica | Si el valor está en la lista | Si la subconsulta devuelve al menos una fila |
| Se detiene al primer resultado | No (recorre toda la lista) | Sí |
| Mejor cuando | La subconsulta devuelve pocos valores | La tabla es grande o la subconsulta es correlacionada |
| Manejo de NULL | NOT IN falla con NULL en la lista | NOT EXISTS no tiene este problema |
Subconsultas en SELECT: columnas calculadas dinámicamente
Una subconsulta en la lista de columnas del SELECT genera un valor calculado para cada fila del resultado. Debe ser escalar: si devuelve más de una fila, MySQL produce un error.
El caso de uso más habitual es calcular un porcentaje o una proporción que requiere un valor de referencia de otra tabla. En lugar de hacer un JOIN completo, la subconsulta recupera ese valor de referencia específico para cada fila.
Subconsultas en FROM: tablas temporales
Una subconsulta en la cláusula FROM genera una tabla temporal que solo existe durante la ejecución de la consulta. Esa tabla temporal recibe un alias y puede consultarse desde el exterior igual que cualquier tabla real.
Es útil cuando se necesita pre-filtrar o pre-agregar datos antes de la consulta principal. En lugar de trabajar con toda la tabla original, la consulta exterior trabaja con un subconjunto ya procesado.
Por ejemplo, si solo interesan países de cierto continente con población superior a un umbral, se puede crear una tabla temporal con esos países filtrados y luego consultar sobre esa tabla temporal en lugar de la tabla original completa.
Cuándo usar JOIN en lugar de subconsulta
Las subconsultas y los JOINs pueden resolver los mismos problemas en muchos casos, pero no siempre con el mismo rendimiento ni la misma legibilidad.
- Usa JOIN cuando necesitas columnas de ambas tablas en el resultado. Una subconsulta en WHERE no puede devolver columnas de la tabla que está consultando.
- Usa JOIN cuando la subconsulta es correlacionada y la tabla tiene muchos registros. Un JOIN suele ser más rápido porque procesa las tablas una sola vez, no una vez por fila.
- Usa subconsulta cuando la lógica es más clara expresada como pregunta dentro de pregunta, o cuando el resultado intermedio no necesita aparecer en el SELECT final.
- En subconsultas correlacionadas con tablas grandes (miles de filas), considera siempre si un JOIN equivalente sería más eficiente. La subconsulta se ejecuta N veces; el JOIN se ejecuta una.
Código y ejemplos
IN: países con ciudades de más de 5 millones
USE world;
-- Países que tienen al menos una ciudad con más de 5 millones de habitantes
-- La subconsulta genera la lista de códigos de país; IN verifica la pertenencia
SELECT name AS pais,
continent AS continente
FROM country
WHERE code IN (
SELECT DISTINCT countrycode -- elimina duplicados: un país puede tener varias ciudades grandes
FROM city
WHERE population > 5000000 -- ciudades con más de 5 millones
);DISTINCT dentro de la subconsulta evita que el mismo código de país aparezca varias veces en la lista (si un país tiene dos ciudades con más de 5M). Sin DISTINCT el resultado de IN es idéntico, pero la lista es mayor y la comparación más lenta.
NOT EXISTS: idiomas hablados en un único país
-- Idiomas que no aparecen en ningún otro país distinto al propio
-- NOT EXISTS se detiene en el primer duplicado encontrado: más eficiente que COUNT + HAVING
SELECT cl1.language AS idioma,
cl1.countrycode AS unico_pais
FROM countrylanguage cl1
WHERE NOT EXISTS (
SELECT 1
FROM countrylanguage cl2
WHERE cl2.language = cl1.language -- mismo idioma
AND cl2.countrycode != cl1.countrycode -- pero en un país distinto
);cl1 y cl2 son dos alias de la misma tabla: la consulta exterior recorre un registro, y la subconsulta busca en el resto si ese idioma aparece en otro país. Si no lo encuentra (NOT EXISTS = true), el idioma es exclusivo.
Subconsulta escalar en SELECT: porcentaje de población
-- Qué porcentaje de la población total de su país representa cada ciudad
SELECT ci.name AS ciudad,
ci.population AS poblacion_ciudad,
co.name AS pais,
co.population AS poblacion_pais,
ROUND(
ci.population * 100.0 /
(SELECT population
FROM country
WHERE code = ci.countrycode), -- subconsulta escalar: población del país de esta ciudad
2
) AS porcentaje_del_pais
FROM city ci
JOIN country co ON ci.countrycode = co.code
WHERE ci.population > 1000000 -- solo ciudades con más de 1 millón
ORDER BY porcentaje_del_pais DESC
LIMIT 20;La subconsulta escalar dentro de ROUND devuelve un único número (la población del país de esa ciudad específica) y se ejecuta una vez por cada fila. El JOIN aporta el nombre del país; la subconsulta aporta el valor de referencia para el porcentaje.
Subconsulta en FROM: tabla temporal
-- Crear una tabla temporal con países de Asia y consultar sobre ella
SELECT continente,
AVG(poblacion) AS poblacion_media
FROM (
SELECT continent AS continente,
population AS poblacion
FROM country
WHERE continent = 'Asia'
AND population > 0
) AS paises_asia -- alias obligatorio para la subconsulta en FROM
GROUP BY continente;
-- Comparar IN vs EXISTS para el mismo resultado
-- Con IN (subconsulta devuelve lista):
SELECT name FROM country
WHERE code IN (SELECT countrycode FROM city WHERE population > 5000000);
-- Con EXISTS (subconsulta correlacionada):
SELECT name FROM country AS co
WHERE EXISTS (
SELECT 1 FROM city
WHERE countrycode = co.code AND population > 5000000
);La subconsulta en FROM debe tener siempre un alias (AS paises_asia). Los dos últimos ejemplos devuelven el mismo resultado: IN genera una lista completa primero; EXISTS se detiene en el primer match por país, siendo más eficiente con tablas grandes.
Errores comunes
Buenas prácticas
Ejercicios
- Usa IN para obtener los nombres de los países que tienen al menos una ciudad con más de 1 millón de habitantes. ¿Cuántos países aparecen?
- Usa EXISTS para obtener los mismos resultados que el ejercicio anterior. Compara la legibilidad y prueba cuál es más intuitivo para ti.
- Usa NOT EXISTS para encontrar países que no tienen ninguna ciudad registrada en la tabla city.
- Escribe una subconsulta escalar en SELECT que muestre, para cada país de Europa, cuántas ciudades tiene (de la tabla city). Ordena de más a menos ciudades.
- Crea una subconsulta en FROM que genere una tabla temporal con solo los países de América del Sur y su población. Luego calcula la población media de esa tabla temporal.
- Combina JOIN y subconsulta: obtén el nombre y continente de los países cuya población total supera el promedio de todos los países (usa una subconsulta escalar para el promedio).
Checklist final
- Entiendo qué es una subconsulta y sé que siempre va entre paréntesis.
- Conozco los tres tipos de subconsulta: escalar (un valor), de lista (múltiples filas) y correlacionada (depende de la consulta exterior).
- Sé usar IN para verificar si un valor pertenece a una lista devuelta por una subconsulta.
- Sé usar EXISTS y NOT EXISTS para verificar presencia o ausencia de correspondencia.
- Entiendo por qué NOT IN falla cuando la subconsulta puede devolver NULL y cómo evitarlo con NOT EXISTS.
- Puedo colocar una subconsulta en SELECT para generar columnas calculadas dinámicamente.
- Puedo colocar una subconsulta en FROM para crear una tabla temporal con alias.
- Sé cuándo es mejor usar JOIN en lugar de subconsulta (tablas grandes, subconsultas correlacionadas).
¿Has terminado la clase?
Márcala como repasada y vuelve cuando quieras revisarla.