Respuesta.

Historia de la cuestión:

Las subconsultas anidadas se diseñaron originalmente en SQL para ampliar las capacidades expresivas del lenguaje y resolver problemas comerciales complejos que no encajan en simples operadores SELECT. Sin embargo, con el aumento de los volúmenes de datos y la complejidad de los modelos relacionales, llegó la comprensión de que no siempre las subconsultas anidadas funcionan de manera eficiente: mucho depende de la implementación del optimizador de la base de datos específica.

Problema:

El principal desafío es encontrar un compromiso entre la legibilidad, la corrección de la lógica y el rendimiento. No siempre una subconsulta anidada se optimiza a operaciones de join, y a menudo se convierte en costosos ciclos de iteración (Nested Loops).

Solución:

Usar subconsultas anidadas en SELECT para calcular agregados o expresiones complejas, si no se puede evitar.
Para grandes volúmenes de datos, pasar a JOIN — esto permite al optimizador hacer la consulta basada en conjuntos y aplicar índices.
Extraer la subconsulta a la parte externa (a través de WITH/CTE), si se necesita mejorar la legibilidad o el rendimiento.

Ejemplo de código:

-- Subconsulta anidada en SELECT (con precaución!)
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.client_id = clients.id) AS order_count
FROM clients;

-- Equivalente a través de JOIN (generalmente más rápido):
SELECT clients.name, COUNT(orders.id) AS order_count
FROM clients
LEFT JOIN orders ON orders.client_id = clients.id
GROUP BY clients.name;

Características clave:

Las subconsultas correlacionadas anidadas a menudo conducen a un rendimiento O(N*M)
Las subconsultas no correlacionadas son más seguras y rápidas
Mover la subconsulta a WITH/CTE o JOIN aumenta la previsibilidad del plan y acelera la ejecución

Preguntas trampa.

¿Funciona una subconsulta anidada en SELECT más rápido que un LEFT JOIN equivalente?

A menudo — no. Una subconsulta correlacionada en SELECT se ejecuta para cada fila de la consulta externa, mientras que el JOIN se construye una vez con índices para toda la tabla.

¿Se puede usar una subconsulta en FROM en lugar de CTE (WITH), y habrá diferencia?

Sí, la subconsulta en FROM:**

SELECT t1.id, sub.agg
FROM table1 t1
JOIN (SELECT id, MAX(val) AS agg FROM table2 GROUP BY id) sub ON t1.id = sub.id;

Pero a veces CTE tiene mayor legibilidad y puede conducir a otra optimización en los planes de ejecución.

¿Se optimizan todas las subconsultas anidadas hasta JOIN análogos?

No. No todas las bases de datos pueden hacerlo de manera equivalente, a veces una subconsulta anidada conduce a un escaneo de cada fila, especialmente si hay correlación entre la consulta externa e interna.

Errores comunes y anti-patrones

Uso de subconsultas correlacionadas anidadas en SELECT con grandes volúmenes.
Duplicación de condiciones de filtrado en la consulta interna y externa.
Intento de reemplazar todas las subconsultas anidadas por JOIN sin analizar las tareas.

Ejemplo de la vida real

Caso negativo

Un gerente de ventas hizo un informe sobre clientes, contando el número de pedidos con una subconsulta interna. Los tiempos de ejecución eran minutos y la carga en el servidor crecía exponencialmente.

Ventajas:

Lógica clara Desventajas:
Informe muy lento con un gran número de clientes
Carga ineficiente en el servidor

Caso positivo

La consulta fue reescrita con LEFT JOIN y agrupación.