Simplifier les requêtes, mot-clé WITH

Fait partie de la section Les fondamentaux du Journey SQL de Coddy. Leçon 49 sur 72.

Les requêtes peuvent devenir trop complexes lorsqu’on y ajoute de nombreuses requêtes internes. Voici, par exemple, une requête qui comporte de nombreuses sous-requêtes :

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
	)
)

Pour simplifier, nous pouvons utiliser le mot-clé WITH query_name AS (...). Il nous permet d’enregistrer une requête sous un nom et de l’utiliser où nous le souhaitons :

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)

Ici, nous avons réutilisé query1 dans query2 et dans la requête principale.

Défi

Facile

Tables et colonnes disponibles :
devices_specs : device_id, width, height, num_features, opinion
devices_score : device_id, score

écris une requête qui :

Identifie d’abord les « appareils grands » (les appareils dont la width est supérieure à 200)
Calcule ensuite la note moyenne de ces appareils grands. Nomme cette colonne average_score

Utilise la clause WITH pour résoudre ce problème.

Essayez vous-même

-- Étape 1 : nommez un ensemble de résultats temporaire qui ne conserve que les appareils qui vous intéressent
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- Étape 2 : réutilisez cet ensemble de résultats temporaire dans la requête principale
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);