Menu
CoddyTech

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

ここでは、query1をquery2で再利用し、さらにメインクエリでも再利用して、上のネストされたバージョンにはないcol4に対する条件をもう1つ追加しました。WITHを使って作成した名前付きクエリは、CTE(共通テーブル式)と呼ばれます。

challenge icon

チャレンジ

簡単

利用可能なテーブルと列:

  • devices_specs: device_id, width, height, num_features, opinion
  • devices_score: device_id, score

次のクエリを書いてください:

  1. まず、「大型デバイス」(width が 200 より大きいデバイス)を特定します。
  2. 次に、これらの大型デバイスの平均スコアを計算します。この列名を 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 (____);
quiz icon腕試し

このレッスンには短いクイズがあります。レッスンを始めて解答し、進捗を記録しましょう。

基礎のすべてのレッスン

自分で練習してみよう: SQLプレイグラウンド