Algunas preguntas requieren dos pasos para responderse. “¿Qué pedidos están por encima del promedio?” requiere que primero encuentre el promedio y luego compare cada orden con él. Subconsultas SQL le permiten escribir ambos pasos como una sola declaración.

Este tutorial cubre cada tipo usando dos tablas pequeñas. Ejecutamos estas consultas en SQLite 3.50 y los resultados mostrados provienen de esa ejecución. La sintaxis de subconsulta estándar es ampliamente compatible y señalamos los lugares donde difieren las bases de datos.

¿Qué es una subconsulta?

Una subconsulta, también llamada consulta interna o anidada, es una declaración SELECT colocada dentro de otra declaración, entre corchetes. Conceptualmente, la consulta interna produce un resultado que luego utiliza la consulta externa.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Las tablas de muestra

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Tenga en cuenta que Dev no tiene órdenes. Ese detalle es importante para varios ejemplos a continuación.

Tipos de subconsultas de un vistazo

Subconsultas escalares

Una subconsulta escalar devuelve exactamente un valor, por lo que puede usarla siempre que un valor único tenga sentido. Aquí está la consulta anterior, con su resultado. El promedio de todos los pedidos es 980:

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

No se pudo escribir WHERE total > AVG(total), porque no se permiten agregados directamente en WHERE. La subconsulta soluciona esto calculando el valor por separado.

¿Qué pasa si una subconsulta “escalar” devuelve varias filas? La mayoría de las bases de datos, incluidas PostgreSQL, MySQL y SQL Server, generan un error. SQLite no lo hace. En nuestra prueba, usó silenciosamente solo la primera fila, lo que podría ocultar un error real, así que no dependa de este comportamiento.

EN y NO EN

Cuando la consulta interna devuelva una lista, utilice IN. Esto encuentra clientes con al menos un pedido superior a 1000:

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Asha
-- Chitra

NOT IN hace lo contrario: encuentra clientes que nunca han realizado un pedido:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Dev

Eso funciona aquí, pero NOT IN tiene un caso extremo peligroso, que cubrimos a continuación.

Subconsultas en FROM

Una subconsulta en FROM actúa como una tabla temporal, a menudo denominada tabla derivada. Es útil cuando necesita agregar dos veces, como en esta consulta para el gasto promedio por cliente en cada ciudad:

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

La consulta interna totaliza cada cliente y la externa promedia esos totales. Dar un alias a la tabla derivada es un buen hábito, ya que algunas bases de datos lo requieren. Si GROUP BY es nuevo para usted, consulte SQL GROUP BY y HAVING.

Subconsultas en SELECT

Una subconsulta escalar también puede agregar una columna calculada. Éste cuenta los pedidos por cliente:

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev aparece con cero. Una unión interna habría eliminado a Dev por completo, porque Dev no tiene filas coincidentes. Podrías obtener el mismo resultado con LEFT JOIN y GROUP BY, lo cual explicamos en Explicación de uniones SQL.

Subconsultas correlacionadas

El ejemplo anterior también está correlacionado, ya que la consulta interna hace referencia a c.id de la consulta externa. Una subconsulta no correlacionada puede ejecutarse sola. Uno correlacionado no puede, porque depende de un valor de la fila exterior actual.

Esto encuentra pedidos por encima del promedio del mismo cliente:

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

Aquí se explica cómo leerlo. Para el pedido 101, la consulta interna promedia los pedidos del cliente 1 (1200 y 800), lo que da 1000. Como 1200 es mayor, la fila se mantiene. Para el pedido 102, se aplica el mismo promedio, pero 800 no es mayor, por lo que se descarta. El pedido único del cliente 2 es igual a su propio promedio, por lo que también se descarta.

“Una vez por fila” describe la lógica, y los optimizadores de consultas a menudo las reescriben en algo mucho más eficiente. Aún así, vale la pena investigar una subconsulta correlacionada lenta. Un índice en la columna interior, aquí orders.customer_id, puede marcar una gran diferencia. Consulte Explicación de los índices SQL.

EXISTE y NO EXISTE

EXISTS hace una pregunta de sí o no: ¿la consulta interna devuelve al menos una fila? Puede detenerse en el primer partido y lo que selecciones dentro no importa, razón por la cual la gente suele escribir SELECT 1.

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Asha, Ben, Chitra

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dev

La trampa NO EN NULL

Este es uno de los errores de subconsulta más comunes. Creamos una tabla con una ID real y una NULL, luego ejecutamos ambos formularios en ella:

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

¿Por qué el primero no devuelve nada? id NOT IN (1, NULL) significa id <> 1 AND id <> NULL. Cualquier comparación con NULL es más desconocida que verdadera, por lo que toda la condición nunca puede ser verdadera. No hay ningún mensaje de error ni advertencia, solo un resultado vacío que parece plausible.

Subconsulta vs JOIN vs CTE

Muchas consultas se pueden escribir de más de una manera. Anteriormente encontramos clientes con un pedido grande usando IN. Aquí está el mismo resultado con una unión:

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

Observe el DISTINCT. Una combinación puede producir duplicados si un cliente tiene varias coincidencias, mientras que IN y EXISTS no pueden.

El rendimiento varía según la base de datos y los datos, y los optimizadores modernos suelen producir planes similares. Así que elija primero la forma más clara y luego marque EXPLAIN si resulta ser lenta.

Errores comunes

• Usando = con un resultado de varias filas. Utilice IN o asegúrese de que la consulta interna devuelva una fila.
• Usando NOT IN en una columna que acepta valores NULL. Puedes perder silenciosamente cada fila.
• Usar alias poco claros. En consultas correlacionadas, califique cada columna o puede vincularse a la tabla incorrecta.
• Anidamiento profundo. Tres niveles son difíciles de leer, así que conviértalos a CTE.
• Agregar ORDER BY adentro. Por lo general, no tiene ningún efecto en el resultado externo y algunas bases de datos lo rechazan.

Conclusión

Las subconsultas son una forma natural de expresar preguntas de dos pasos. Comience con subconsultas escalares y IN, luego practique las correlacionadas razonando a través de una sola fila exterior a la vez. Y recuerde NOT EXISTS siempre que sean posibles NULL. Para practicar, escriba una pregunta de tres maneras, con una subconsulta, una combinación y un CTE, y compare los resultados y la legibilidad.

Preguntas frecuentes

sigue aprendiendo