WITHキーワードでクエリを簡潔に

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 (SELECT col1 FROM query2) AND col4 IN (SELECT col5 FROM query1)

ここでは、query2 とメインクエリで query1 を再利用しました。

チャレンジ

簡単

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

次の処理を行うクエリを書いてください：

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

WITH 句を使用してこの問題を解決してください。

自分で試してみよう

-- ステップ 1: 関心のあるデバイスのみを保持する一時的な結果セットに名前を付ける
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- ステップ 2: その一時的な結果セットをメインクエリ内で再利用する
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);