Simplify queries, WITH keyword

Parte de la sección Fundamentos del Journey de SQL de Coddy — lección 49 de 72.

Las consultas pueden volverse demasiado desordenadas al agregar muchas consultas internas. Por ejemplo, aquí hay una consulta que tiene muchas subconsultas:

SELECT * FROM table1
WHERE col2 IN (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN (
		SELECT col5 FROM table3
		WHERE col1 AND col3 OR col2
	)
)

Para hacerlo más fácil, podemos usar la palabra clave WITH query_name AS (...). Nos permite guardar una consulta con un nombre y usarla donde queramos:

WITH query1 AS (
	SELECT col5 FROM table3
	WHERE col1 AND col3 OR col2
), query2 AS (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN query1
)
SELECT * FROM table1
WHERE col2 IN query2 AND col4 IN query1

Aquí reutilizamos query1 en query2 y en la consulta principal.

Desafío

Fácil

Tablas y columnas disponibles:
devices_specs: device_id, width, height, num_features, opinion
devices_score: device_id, score

escribe una consulta que:

Primero identifica "dispositivos grandes" (dispositivos con width mayor que 200)
Luego calcula la puntuación promedio para estos dispositivos grandes. Llama a esta columna como average_score

Usa la cláusula WITH para resolver este problema.

Hoja de referencia

Utilice WITH query_name AS (...) para crear subconsultas con nombre que se pueden reutilizar:

WITH query1 AS (
    SELECT col5 FROM table3
    WHERE col1 AND col3 OR col2
), query2 AS (
    SELECT col1 FROM table2
    WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN query1
)
SELECT * FROM table1
WHERE col2 IN query2 AND col4 IN query1

Esta aproximación hace que las consultas complejas con múltiples subconsultas sean más legibles y permite reutilizar la misma subconsulta varias veces.

Pruébalo tú mismo