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 subconsultas. 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 (SELECT col1 FROM query2) AND col4 IN (SELECT col5 FROM 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 identifique los "dispositivos grandes" (dispositivos con width mayor a 200)
Después calcule el puntaje promedio para estos dispositivos grandes. Nombra a esta columna como average_score

Usa la cláusula WITH para resolver este problema.

Pruébalo tú mismo

-- Paso 1: nombra un conjunto de resultados temporal que conserve solo los dispositivos que te importan
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- Paso 2: reutiliza ese conjunto de resultados temporal dentro de la consulta principal
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);

Ponte a prueba

Esta lección incluye un breve cuestionario. Empieza la lección para responderlo y registrar tu progreso.

Todas las lecciones de Fundamentos

3Specific Return Format

Null values Sort Results Part 1 Sort Results Part 2 Recap - Cyber Security Firm Limit number of records Recap - Vehicle Factory

6Intro Challenges

Recap - Parliamentary Election Recap - Police Criminal Arrest Recap - Bar Beverage Container Recap - Engineer new columns

9Multiple tables

Basic Join Part 1 Basic Join Part 2 Recap - Join Self join Recap - Self Join UnionSimplify queries, WITH keywordRecap - With Queries Recap - Real Estate Contractor

12Window Functions part 2

RANK & DENSE_RANK Functions Recap - RANK & DENSE_RANK NTILE Function Aggregation Functions ROWS & RANGE Criterion

Practica por tu cuenta: Playground de SQL