SQLについて整理のためのメモ

正直にいってJavaScriptからプログラミングの世界に入ってプレゼンテーション領域ばかり見てきた人間には、Javaやその他の手続き型オブジェクト指向言語のだいたいの“動き”は予測がつくものであっても、SQLの“動き”というのはそうでもない。

とくにGROUP BY句がクエリ内に登場するときの結果表について、よく自信がないままともかくトライ＆エラーで、という運びになるケースがあってみっともない。というわけでメモ。サンプルの引用元は『プログラマのためのSQL 第2版』。

プログラマのためのSQL 第2版

大前提として、GROUP BY句によるデータ操作には、FROM句とWHERE句の結果が使用されます。

-- 前掲書282ページ上段の例：
SELECT
    state,
    city,
    COUNT(*)
FROM
    Villes
GROUP BY
    states
    -- ↑↑↑
    -- FROM（および存在しないがWHERE）で抽出された結果のうち
    -- statesがグループ化するためのカラムとして指定されている。
;

GROUP BY句によってグループ化されたテーブル（グループ化テーブル）の各行のカラムの値は、あくまでもグループの性格をあらわすものであって、そういう意味でstateやCOUNT(*)の値は意味あるものですが、cityはそうではありません。推測で言っていますが、cityにどんな値が入ってくるかは、SQL標準のあずかり知らぬところでしょう。

さて、だけど、正確に言うと、FROM句とWHERE句で絞り込まれたレコードには、まずSELECT句が適用されていると考えるべきのようです（以下の記述はすべて、「SQL構文を解析して結果を返す種々のデータベース製品の個別の実装はどうあれ、次のように考えると論理的に納得がいく」というものです）。
したがって（よく知られているように）上記の例にあるSELECT句のstateを除いてしまうと、クエリは構文エラーとなってしまいます。

-- このクエリはエラー：
SELECT
    city,
    COUNT(*)
FROM
    Villes
GROUP BY
    states
;

GROUP BY句は、SELECT句で指定・抽出されたカラムのみからなるレコードの集合に対して、適用されるわけです。

けれどもSELECTが適用されるというのは、別にSELECT句に書かれた何もかもが評価されて値に変換されるわけではありません。そんなことがあれば、COUNT(*)の結果はVillesテーブルのすべてのレコード数になってしまい、GROUP BY句の意味もなくなってしまいます。

したがってSELECT句の記述内容は、集合関数に関して遅延評価されているように見えます。

FROM句とWHERE句で抽出されたレコードの集合は、SELECT句によるカラムの選別の対象になります。その際にstatesやcityはそれ以上簡約できない、分解できない値として評価されて（つまり具体的な値として決定されて）そのまま結果セットを構成します。

けれどもCOUNT(*)は評価を遅延されます。評価がなされるのはGROUP BY句とHAVING句による操作が終わった後です。COUNT(*)はグループ化カラムとして、評価が保留されます。

GROUP BY句によって、SELECT句が適用されたレコードの集合にが特定のカラムの値によってグループ化されます。このとき元になったレコードの集合と、それをもとに構成されたグループ化テーブルの関係はどうなっているのでしょう。

グループ化テーブルに含まれるレコード（の各カラム）は、あくまでもそのグループの性格を示すものです。そしてそのグループはグループ化までのもとのレコードの集合への参照を保っているように見えます。

そしてここでもSELECT句に含まれていた集合関数の評価は遅延されています。ここでHAVING句が登場します。HAVING句はグループ化されたレコードの集合に適用されます。

-- 前掲書283ページ中段の例：
SELECT
    deptno
FROM
    Employees
WHERE
    job = 'Programmer'
GROUP BY
    deptno
HAVING
    COUNT(*) < 5
;

もしGROUP BY句の時点でCOUNT(*)が計算されていたら、HAVING句のCOUNT(*)の意味は変わってきてしまうでしょう。SELECT句はもちろんGROUP BY句の時点でも関数の評価が遅延されていてこそ、HAVING句におけるCOUNT(*)も意味を持ってきます。HAVING句のCOUNT(*)は、GROUP BY句で生成されたグループ化テーブルのレコードを経由して、各グループ・レコードのもとになったレコードの集合にアクセスしてその数をカウントしているわけです。

したがって以下のクエリは失敗します。

-- 前掲書284ページ中段の例：
SELECT
    deptno
FROM
    Employees
GROUP BY
    deptno
HAVING BY 
    (COUNT (*) < 5)
    AND
    (job = 'Programmer')
;

正しくはこうですね。

SELECT
    deptno,
    job
FROM
    Employees
GROUP BY
    deptno,
    job
HAVING BY 
    (COUNT (*) < 5)
    AND
    (job = 'Programmer')
;

SELECT句でもGROUP BY句でも、COUNT(*)は評価を保留されましたが、HAVING句では異なります。HAVING句に記述された式と関数は、その場で完全に評価されて真偽の判定が行われます。COUNT(*)は完全に評価されて具体的な数値に変換されます。そしてCOUNT(*) < 5という式全体が評価されます。

FROM → WHERE → （SELECT →） GROUP BY → HAVINGと来て、取得とレコード・フィルタリング、グループ化とグループ化レコード・フィルタリングを通過したレコードの集合は、最後に評価されてクライアントに戻されます。

ここではHAVING句と同じレベルで評価が実施されます。つまり集合関数が評価され、具体的な値、それ以上簡約が不可能な値に変換ます。結果テーブルを構成するレコードがグループ化レコードであった場合、それらのレコードはその元になったグループを構成するレコードへの参照のようなメタレベルのデータを失って、簡約不可能な値のカラムからなるレコードになります。