Simplify queries, WITH keyword

CoddyのSQLジャーニー「基礎」セクションの一部 — レッスン 49/72。

多くの内部クエリを追加するとクエリが乱雑になりがちです。たとえば、多くのサブクエリを含むクエリを以下に示します：

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

より簡単に実現するために、WITH query_name AS (...) キーワードを使用できます。これにより、クエリに名前を付けて保存し、必要な場所で使用できます：

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

ここでは query1 を query2 とメインクエリで再利用しています。

チャレンジ

簡単

利用可能なテーブルと列：
devices_specs: device_id, width, height, num_features, opinion
devices_score: device_id, score

次のことを行うクエリを書いてください：

まず「大型デバイス」（width が 200 より大きいデバイス）を特定します
次に、これらの大型デバイスの平均スコアを計算します。この列を average_score と名付けます

この問題を解決するために WITH 句を使用してください。

チートシート

WITH query_name AS (...) を使用して、再利用可能な名前付きサブクエリを作成します：

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

この手法により、複数のサブクエリを含む複雑なクエリをより読みやすくし、同じサブクエリを複数回再利用できます。

自分で試してみよう