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(共通テーブル式)と呼ばれます。
チャレンジ
簡単利用可能なテーブルと列:
devices_specs:device_id,width,height,num_features,opiniondevices_score:device_id,score
次のクエリを書いてください:
- まず、「大型デバイス」(
widthが 200 より大きいデバイス)を特定します。 - 次に、これらの大型デバイスの平均スコアを計算します。この列名を
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 (____);このレッスンには短いクイズがあります。レッスンを始めて解答し、進捗を記録しましょう。
基礎のすべてのレッスン
自分で練習してみよう: SQLプレイグラウンド