3.8 入れ子状のクエリ

より複雑なクエリが必要になる時があります. たとえば employees テーブルをグループ別集計し, その結果と departments テーブルで結合する, といった処理がしたい場合, ここまで紹介した構文だけでは難しいです. このような場合の解決法が2つあります. 1つめはクエリの中にクエリを入れ子にする, サブクエリを利用することです. employees テーブルを deparment_id でグループ化し, 人数を集計してから departments の部署名と紐付けたい場合を考えます. 結合してから集計することも可能ですが, テーブルが巨大になる場合, 集計を先に行ったほうが計算量が減るという利点があります.

SELECT t2.id,  t2.name,  t1.num
FROM (
  SELECT department_id,  count(1) as num
  FROM <DB>.employees
  GROUP BY department_id
) t1
LEFT JOIN
  <DB>.departments t2
ON t1.department_id = t2.id

このように, これまでテーブルを指定していたところに, 別のクエリを書き, そのクエリの結果をテーブルのように扱うことができます. これをサブクエリといいます. 特に, 階層が上のクエリでサブクエリの集計前のテーブルを参照しておらず, かつ処理が独立しているこのようなタイプのサブクエリは特に, 非相関サブクエリと呼ばれます.

もう1つのやり方として, WITH 句の利用があります. WITH 句は TD で使えますが, SQL の標準機能ではないため対応していないエンジンも存在することに注意してください. 上記のクエリと同等の処理は, 以下のように書くことができます.

WITH sub AS (
  SELECT department_id,  count(1) as num
  FROM <DB>.employees
  GROUP BY department_id
)

SELECT t2.id,  t2.name,  t1.num
FROM sub t1
LEFT JOIN
  <DB>.departments t2
ON t1.department_id = t2.id

WITH 句は AS の後の括弧内のクエリの内容を, キーワードで置き換えるということを意味します. 今回の場合, employees の集計結果を sub という仮想的なテーブルに置き換えています. カンマで区切ることで, 複数のテーブルを設定できます.