Subconsultas escalares y en WHERE

¿Qué es una subconsulta?

Una subconsulta (o consulta anidada) es una consulta SELECT escrita dentro de otra consulta, entre paréntesis. La interna se ejecuta primero y su resultado alimenta a la externa. Es la forma natural de responder preguntas que dependen de un dato que hay que calcular antes.

Subconsulta escalar

Una subconsulta escalar devuelve un único valor (una fila y una columna). Por eso puedes usarla en cualquier sitio donde encaje un valor, como el lado derecho de una comparación:

SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos)
ORDER BY precio DESC;

Primero se calcula AVG(precio) (un número), y luego se comparan las filas contra él. Si la subconsulta devolviera más de una fila aquí, SQLite daría error: una comparación con > espera un solo valor.

Truco mental: lee la subconsulta como si fuera una constante que aún no conoces. "Productos más caros que la media".

Subconsultas con IN

Cuando la subconsulta devuelve una columna con varios valores, no puedes usar =, pero sí IN, que comprueba pertenencia a un conjunto:

SELECT nombre
FROM productos
WHERE categoria_id IN (
  SELECT id FROM categorias WHERE nombre IN ('perifericos', 'pantallas')
)
ORDER BY nombre;

La subconsulta produce la lista de id de esas categorías; la externa se queda con los productos cuyo categoria_id está en esa lista. Su opuesto es NOT IN.

Cuidado con NULL: si la subconsulta de un NOT IN puede devolver NULL, el resultado se vuelve impredecible. En esos casos suele preferirse NOT EXISTS (lección siguiente).

Ejemplos

Productos por encima del precio medio

-- Sustituye mentalmente la subconsulta por su valor (la media):
SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos)
ORDER BY precio DESC;

IN con una lista calculada por otra consulta

SELECT nombre
FROM productos
WHERE categoria_id IN (SELECT id FROM categorias WHERE nombre = 'ordenadores')
ORDER BY nombre;