Conversión de subconsultas en uniones

No todas las consultas son iguales, especialmente en términos de rendimiento. En este artículo, veremos cómo se pueden convertir las subconsultas SQL en uniones para mejorar la eficiencia.

¿Cuándo debo utilizar las subconsultas SQL?

Gran pregunta. Por desgracia, no hay una respuesta concreta. Los principiantes de SQL tienden a utilizar en exceso las subconsultas. Normalmente, una vez que descubren que la construcción de SQL funciona en una situación, intentan aplicar ese mismo enfoque a otras situaciones. Es natural.

Sin embargo, en algunos casos una subconsulta puede ser reemplazada por un JOIN más eficiente. Si puede evitar una subconsulta y sustituirla por una cláusula JOIN, debería hacerlo sin dudarlo. Pero, por supuesto, en algunos casos, utilizar una subconsulta es la única manera de resolver una cuestión de datos. En este artículo, mostraré ejemplos de ambos casos: cuando una subconsulta es imprescindible y cuando una subconsulta debe ser evitada y reemplazada por un JOIN.

Los datos

Antes de pasar a los ejemplos, veamos brevemente la base de datos de ejemplo que utilizaremos. La base de datos tiene dos tablas que representan las estadísticas de producción de una empresa ficticia de cultivo de manzanas llamada EverRed. La empresa tiene tres granjas.

La primera tabla es current_year_productionque contiene información sobre el número de manzanas producidas en el año en curso por cada explotación, así como la superficie y el número de árboles de cada explotación. La segunda tabla, production_historyalmacena información sobre la producción anterior de cada explotación. A continuación se muestran algunos datos de ejemplo de estas dos tablas.

Ejemplo: sustitución de una subconsulta por un JOIN

Suponga que es un analista de datos SQL que trabaja en EverRed. El propietario de la empresa quiere que obtengas los nombres de las granjas en las que la empresa está produciendo más manzanas en el año actual que en el año anterior (2017).

Solución utilizando una subconsulta:

SELECT farm_name, 
FROM current_year_production CYP
WHERE production_in_kg > (
  SELECT production_in_kg 
  FROM production_history PH  
  WHERE PH.farm_id = CYP.farm_id
  AND year = 2017
) 

Solución utilizando una cláusula JOIN:

SELECT farm_name, 
FROM current_year_production CYP 
JOIN production_history PH
  ON PH.farm_id = CYP.farm_id 
WHERE PH.year = 2017
  AND CYP.production_in_kg > PH.production_in_kg 

La diferencia entre estos dos enfoques está en el rendimiento. Mientras que la cláusula JOIN del segundo ejemplo sólo necesita ejecutarse una vez, la subconsulta del primer ejemplo se ejecutará una vez por explotación. En este caso, sólo tenemos tres granjas, por lo que la diferencia es insignificante. Pero, ¿qué pasaría si usted trabajara para una empresa más grande que tiene 10.000 granjas globales? La subconsulta tendría que ejecutarse 10.000 veces. Claramente, una subconsulta es ineficiente para nuestros propósitos aquí.

Además, en una base de datos de prueba con sólo unas pocas granjas, ambas consultas se ejecutan con un tiempo de respuesta aceptable; sin embargo, cuando pasamos a una base de datos productiva, (donde el volumen de datos suele ser mucho mayor), el tiempo de respuesta del enfoque de subconsulta aumentará significativamente, mientras que el tiempo de respuesta del enfoque JOIN se mantendrá estable.

El resultado de las dos consultas anteriores es equivalente:

Ejemplo: cuando las subconsultas son el único camino a seguir

Supongamos ahora que el propietario de la empresa, después de leer los resultados que ha entregado en la consulta anterior, le pide que obtenga los nombres de las explotaciones que están produciendo este año más manzanas por metro cuadrado que la media histórica. Esto parece complejo, pero es más fácil de lo que parece.

Solución mediante una subconsulta:

SELECT
  farm_name, 
  production_in_kg / area AS "production_per_meter"
FROM Current_year_production 
WHERE production_in_kg / area > (
  SELECT AVG(PH.production_in_kg / CYP.area) 
  FROM production_history PH 
  JOIN Current_year_production CYP  
    ON PH.farm_id = CYP.farm_id 
) 

No podemos sustituir esta subconsulta por un JOIN porque no tenemos una tabla con la media previamente calculada. Es decir, tenemos que calcular primero la media histórica. Y para ello, necesitamos un GROUP BY, que puede romper la relación uno a uno necesaria para un JOIN. Otro punto a tener en cuenta es que la métrica "manzanas por metro cuadrado" se obtiene con la siguiente expresión:

	production_in_kg / area

Utilizamos la métrica "manzanas por metro cuadrado" porque necesitamos alguna forma de comparar la productividad de las distintas explotaciones y clasificarlas. La "producción_en_kg" total de una explotación no es un valor comparable, porque es probable, por ejemplo, que la explotación más grande tenga una mejor production_in_kg. Así que dividimos la "producción_en_kg" por la superficie de cada explotación para estandarizar los valores y crear una métrica comparable.

Encontramos que la producción media histórica por metro cuadrado es de 0,42. Entonces el resultado de la consulta anterior es:

Ejemplo: cuando los JOINs y las subconsultas son igualmente eficientes

Como última consulta de datos, vamos a intentar obtener los años en los que la empresa produjo menos manzanas que en el año actual. Podemos escribir esta consulta utilizando dos enfoques diferentes.

Solución utilizando una subconsulta:

SELECT year, sum(production_in_kg) 
FROM production_history PH
GROUP BY year
HAVING sum(production_in_kg) < (
  SELECT sum(production_in_kg)
  FROM current_year_production
)

Solución utilizando una cláusula JOIN:

SELECT year, sum(PH.production_in_kg) 
FROM production_history PH 
JOIN current_year_production CYP
  ON PH.farm_id = CYP.farm_id
GROUP BY year
HAVING sum(PH.production_in_kg) < sum(CYP.production_in_kg)

Puede ver que ambas consultas son muy similares; la principal diferencia está en la cláusula JOIN y en la subconsulta. En este caso, ambas consultas son igualmente eficientes: la subconsulta se ejecuta una vez en la cláusula HAVING, por lo que no hay problemas de rendimiento.

Pruébelo usted mismo.

Para terminar, es importante notar que las subconsultas y los JOINs son recursos realmente importantes para un desarrollador de SQL. Vimos ejemplos donde podemos reemplazar una subconsulta con un JOIN y ejemplos donde no podemos hacer tal reemplazo. Y a veces, las subconsultas y los JOINs son igualmente eficientes.

Pero, ¿cómo saber cuándo usar una subconsulta y cuándo usar un JOIN? Sinceramente, la única manera de desarrollar su intuición es resolver regularmente ejercicios de SQL. Si quiere perfeccionar sus conocimientos de SQL, nuestro Ejercicios prácticos de SQL ofrece 88 problemas de práctica para un repaso completo.

¡Esté atento a más artículos!