Subconsulta vs. JOIN

Uno de los desafíos al escribir consultas SQL es elegir si se debe usar una subconsulta o un JOIN. Hay muchas situaciones en las que un JOIN es la mejor solución, y hay otras en las que una subconsulta es mejor. Consideremos este tema en detalle.

Las subconsultas se usan en consultas SQL complejas. Normalmente, hay una consulta principal externa y una o más subconsultas anidadas dentro de la consulta externa.

Las subconsultas pueden ser simples o correlacionadas. Las subconsultas simples no dependen de las columnas de la consulta externa, mientras que las subconsultas correlacionadas hacen referencia a los datos de la consulta externa.

Puedes aprender sobre las subconsultas en la sección Subconsultas del curso interactivo "SQL para principiantes " o practicar la escritura de subconsultas en la sección Subconsultas del curso "Ejercicios prácticos de SQL". O simplemente leer el artículo "Subconsultas SQL" de María Alcaraz.

La cláusula JOIN no contiene consultas adicionales. Conecta dos o más tablas y selecciona datos de ellas en un único conjunto de resultados. Se utiliza con mayor frecuencia para unir tablas con claves primarias y foráneas. Puede practicar Tipos de JOIN en SQL en nuestro curso interactivo Tipos de JOIN en SQL interactivo. Contiene más de 90 ejercicios para repasar y practicar diferentes tipos de JOINs. También puedes leer más sobre JOINs en el artículo "Cómo practicar Tipos de JOIN en SQL" de Emil Drkušić.

Tanto las subconsultas como JOINpueden ser utilizadas en una consulta compleja para seleccionar datos de múltiples tablas, pero lo hacen de diferentes maneras. A veces se puede elegir cualquiera de las dos, pero hay casos en los que una subconsulta es la única opción real. A continuación describiremos los distintos escenarios.

Considere dos tablas simples product y saleque utilizaremos en nuestros ejemplos.

Aquí está la tabla product tabla.

Esta tabla contiene las siguientes columnas:

• id: el identificador del producto.
• nameEl nombre del producto.
• costel coste del producto
• yearel año de fabricación del producto
• cityla ciudad en la que se fabricó el producto.

Y la otra tabla, sale:

que tiene las siguientes columnas:

• idel identificador de la venta.
• product_idel identificador del producto vendido
• priceel precio de venta
• yearel año en que se vendió el producto.
• cityla ciudad donde se vendió el producto.

Utilizaremos estas dos tablas para escribir consultas complejas con subconsultas y JOINs.

Cuándo reescribir subconsultas con JOINs

Los principiantes en SQL suelen utilizar subconsultas cuando se pueden obtener los mismos resultados con JOINs. Aunque las subconsultas pueden ser más fáciles de entender y utilizar para muchos usuarios de SQL, JOINs suele ser más eficiente. JOINs también es más fácil de leer a medida que las consultas se vuelven más complejas. Por lo tanto, nos centraremos primero en cuándo se puede sustituir una subconsulta por una JOIN para mejorar la eficiencia y la legibilidad.

Subconsulta escalar

El primer caso es la subconsulta escalar. Una subconsulta escalar devuelve un único valor (una columna y una fila) que será utilizado por la consulta externa. He aquí un ejemplo.

Supongamos que necesitamos los nombres y los costes de los productos que se vendieron por 2.000 dólares.

Veamos el código con una subconsulta:

SELECT name, cost 
FROM product
WHERE id=(SELECT product_id 
  FROM sale 
    WHERE price=2000 
    AND product_id=product.id
  );

y el resultado:

La consulta externa selecciona los nombres (name) y el coste (cost) de los productos. Como no queremos todos los productos, utilizamos una cláusula WHERE para filtrar las filas a los ID de los productos devueltos por la subconsulta.

Ahora veamos la subconsulta. La tabla sale contiene los registros de venta de los productos. La subconsulta primero filtra los registros a sólo aquellos con el precio de venta igual a 2.000 dólares (price=2000). A continuación, utiliza los ID de los productos (product_id) en las ventas seleccionadas para identificar los registros de la tabla product tabla (product_id=product.id). Se trata de una subconsulta correlacionada, ya que la segunda condición de la subconsulta hace referencia a una columna de la consulta externa. Sólo dos productos se vendieron a 2.000 dólares: el sillón y la mesa de televisión.

Esta consulta no es muy eficiente. ¿Cómo debemos modificarla?

Podemos construir una estructura JOIN y obtener el mismo resultado. Mire la consulta con un JOIN:

SELECT p.name, p.cost 
FROM product p 
JOIN sale s ON p.id=s.product_id
WHERE s.price=2000;

En esta consulta, conectamos las dos tablas product y sale con un operador JOIN. En la condición JOIN, los registros de la tabla product están vinculados a los registros de la tabla sale tabla a través de los ID de los productos. Al final, las filas se filtran mediante una cláusula WHERE para seleccionar el registro cuando el precio de venta del producto es igual a 2.000 dólares.

Subconsulta dentro de la cláusula IN

Otra subconsulta que se puede sustituir fácilmente por una JOIN es la que se utiliza en un operador IN. En este caso, la subconsulta devuelve a la consulta externa una lista de valores.

Supongamos que queremos obtener los nombres y los costes de los productos vendidos en nuestro ejemplo.

SELECT name, cost 
FROM product 
WHERE id IN (SELECT product_id FROM sale);

La consulta externa selecciona los nombres y los costes de los productos; a continuación, filtra los registros cuyos ID de producto pertenecen a la lista devuelta por la subconsulta. La subconsulta selecciona los ID de los productos de la tabla sale (SELECT product_id FROM sale), por lo que sólo los productos vendidos son devueltos por esta consulta en el conjunto de resultados final, así:

Hay más productos en la tabla product pero sólo se han vendido cuatro.

La siguiente consulta devuelve el mismo resultado utilizando un JOIN:

SELECT DISTINCT p.name, p.cost 
FROM product p 
JOIN sale s ON s.product_id=p.id;

Se convierte en una consulta muy sencilla. Conecta las dos tablas por ID de producto y selecciona los nombres y los costes de estos productos. Es un INNER JOIN, así que si un producto no tiene su ID en la sale tabla, no será devuelto.

Observe que también utilizamos la palabra clave DISTINCT para eliminar los registros duplicados. Esto suele ser necesario si se transforman subconsultas con un IN o un NOT IN en JOINs.

¿Quiere aprender más sobre las subconsultas SQL con el operador IN? Mira un episodio de nuestra serie We Learn SQL en Youtube. Recuerda suscribirte a nuestro canal.

Subconsulta en la Cláusula NOT IN

Esta situación es igual a la anterior, pero aquí la subconsulta se utiliza en un operador NOT IN. Queremos seleccionar los nombres y los costos de los productos que no fueron vendidos.

A continuación se muestra un ejemplo con una subconsulta dentro del operador NOT IN:

SELECT name, cost 
FROM product 
WHERE id NOT IN (SELECT product_id FROM sale);

Los resultados:

La subconsulta devuelve los ID de los productos de la tabla sale (los productos vendidos) y los compara con los ID de los productos en la consulta externa. Si un registro de la consulta externa no encuentra su ID de producto en la lista devuelta por la subconsulta, se devuelve el registro.

¿Cómo se reescribe esta subconsulta con un JOIN? Se puede hacer así:

SELECT DISTINCT p.name, p.cost
FROM product p 
LEFT JOIN sale s ON s.product_id=p.id 
WHERE s.product_id IS NULL;

Esta consulta conecta las dos tablas product y sale por los ID de los productos. También debe utilizar la palabra clave DISTINCT, como hicimos al transformar la subconsulta anterior con un IN en un JOIN.

Observe que al reescribir la subconsulta en el NOT IN, utilizamos un LEFT JOIN y un WHERE. De esta manera, se comienza con todos los productos incluyendo los no vendidos, luego se seleccionan sólo los registros que son NULL en la columna product_id. El NULL denota que el producto no fue vendido.

Subconsultas correlacionadas en EXISTS y en NOT EXISTS

Las subconsultas en un EXISTS o en un NOT EXISTS también son fáciles de reescribir con JOINs.

La siguiente consulta utiliza una subconsulta para obtener los detalles de los productos que no se vendieron en 2020.

SELECT name, cost, city
FROM product  
WHERE NOT EXISTS ( SELECT id  
  FROM sale WHERE year=2020 AND product_id=product.id );

El resultado:

Para cada producto de la consulta externa, la subconsulta selecciona los registros cuyo año de venta es 2020 (year=2020). Si no hay registros para un producto determinado en la subconsulta, la cláusula NOT EXISTS devuelve true.

El conjunto de resultados contiene los productos con el año de venta distinto de 2020, así como los productos sin ningún registro en la sale tabla. Puede reescribir la misma consulta utilizando un JOIN:

SELECT p.name, p.cost, p.city FROM product p 
LEFT JOIN  sale s ON s.product_id=p.id 
WHERE s.year!=2020 OR s.year IS NULL;

En este caso, conectamos la tabla product con la tabla sale mediante un operador LEFT JOIN. Esto nos permite incluir los productos que nunca se vendieron en el conjunto de resultados. La cláusula WHERE filtra los registros seleccionando los productos que no tienen registros en la tabla sale (s.year IS NULL) así como los productos con un año de venta distinto a 2020 (s.year!=2020).

Cuando no se puede sustituir una subconsulta por un JOIN

JOINpuede ser eficiente, pero hay situaciones que requieren una subconsulta y no un JOIN. A continuación se muestran algunas de estas situaciones.

Subconsulta en FROM con un GROUP BY

La primera de ellas es una subconsulta en una cláusula FROM que utiliza un GROUP BY para calcular los valores agregados.

Veamos el siguiente ejemplo:

SELECT city, sum_price  
 FROM  
(
  SELECT city, SUM(price) AS sum_price FROM sale 
  GROUP BY city 
) AS s
WHERE sum_price < 2100;

y el resultado:

Aquí, la subconsulta selecciona las ciudades y calcula la suma de los precios de venta por ciudad. La suma de todos los precios de venta en cada ciudad de la sale tabla se calcula mediante la función de agregación SUM(). Utilizando los resultados de la subconsulta, la consulta externa selecciona sólo las ciudades cuyo precio de venta total es inferior a 2.100 dólares (WHERE sum_price < 2100). Deberá recordar de las lecciones anteriores cómo utilizar los alias para las subconsultas y cómo seleccionar un valor agregado en una consulta externa.

Subconsulta que devuelve un valor agregado en una cláusula WHERE

Otra situación en la que no se puede reescribir una estructura de subconsulta con un JOIN es un valor agregado que se compara en una cláusula WHERE. Observe este ejemplo:

SELECT name FROM product
WHERE cost<(SELECT AVG(price) from sale);

El resultado:

Esta consulta recupera los nombres de los productos cuyo coste es inferior al precio medio de venta. El precio medio de venta se calcula con la ayuda de la función de agregación AVG() y es devuelto por la subconsulta. El coste de cada producto se compara con este valor en la consulta externa.

Subconsulta en una cláusula ALL

Otra situación es una subconsulta con una cláusula ALL.

SELECT name FROM product
WHERE cost > ALL(SELECT price from sale);

La subconsulta devuelve todos los precios de venta de la tabla sale tabla. La consulta externa devuelve el nombre del producto con el precio de venta más alto que el coste.

El resultado:

Cuándo utilizar una subconsulta frente a un JOIN

Hemos revisado algunos usos comunes de las subconsultas y las situaciones en las que algunas subconsultas pueden ser reescritas con JOINs en su lugar. Un JOIN es más eficiente en la mayoría de los casos, pero hay casos en los que no es posible utilizar otras construcciones que no sean una subconsulta. Mientras que las subconsultas pueden ser más legibles para los principiantes, JOINs es más legible para los codificadores de SQL con experiencia a medida que las consultas se vuelven más complejas. Es una buena práctica evitar múltiples niveles de subconsultas anidadas, ya que no son fácilmente legibles y no tienen un buen rendimiento. En general, es mejor escribir una consulta con JOINs que con subconsultas si es posible, especialmente si las subconsultas están correlacionadas.

Si está interesado en aprender más o si quiere practicar sus habilidades, consulte las secciones de subconsultas en el curso "SQL para principiantes" o en el curso "Ejercicios prácticos de SQL".