Simplify queries, WITH keyword

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

Les requêtes peuvent devenir trop désordonnées en ajoutant de nombreuses requêtes internes. Par exemple, voici 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 (...). Cela nous permet d'enregistrer une requête avec 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 query2 AND col4 IN 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

Écrivez une requête qui :

Identifie d'abord les « large devices » (appareils avec width supérieure à 200)
Calcule ensuite la moyenne des scores pour ces grands appareils. Nommez cette colonne average_score

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

Aide-mémoire

Utilisez WITH query_name AS (...) pour créer des sous-requêtes nommées qui peuvent être réutilisées :

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

Cette approche rend les requêtes complexes avec plusieurs sous-requêtes plus lisibles et permet de réutiliser la même sous-requête plusieurs fois.

Essayez vous-même