woshidan's loose leaf

ぼんやり勉強しています。

ドンドン身に付く、スラスラ書ける書き込み式SQLドリル 2章読んだ

1章やっているときは、最初の方に出てきたキーワードを後でやり直さなきゃと思っていたのですが、杞憂でした。
2章以降、遠慮なく1章で使った要素がばんばん出てきて、長いSQLをがんがん書かされる感じ。

よいです。

2章

2-1

JOINキーワードを使うとき、テーブル名にASを使っていいというのを知らなかったので、驚きました。

FROM
    employee AS e
  INNER JOIN
    depart AS d
  ON
    e.depart_id = d.depart_id

SELECT命令を出した時は、内部的にはまずテーブルがあるかどうか、次にその列名があるかどうかを検査して回る。

そこで、SELECT句に入れた列名にテーブル名を書いていなかった場合、複数のテーブルがあったりすると、どのテーブルの列名なのかを考えず全部のテーブルで指定された列が存在するかのチェックを始めてしまう。

逆に、きちんと列名にテーブル名を書いておくと、走査するのはそのテーブルだけでよいので、少しだけオーバーヘッドを軽減できる。

2-2

LEFT JOINは左側のテーブルの行をすべて選択する。
左側のテーブルがあって、それに右側の行をあわせてつなぐ。条件が合わない行はNULLを入れる。
RIGHT JOINはまず右側のテーブルがあって、それに左側の行をあわせてつなぐ。
LEFT OUTER JOINとLEFT JOINがまじってLEFTER JOINと書いていた行があった。

幽霊部署の洗い出しの問題。

左外部結合で2つのテーブルを結合した際に、右側のテーブルの外部キーがNULLであるかどうかを確認することで、対応する行が存在しないことを確認できます

つまり

FROM
    depart AS d
  LEFT JOIN
    employee AS e
  ON
    d.depart_id = e.depart_id
GROUP BY
  e.depart_id
HAVING
  COUNT(e.s_id) = 0

ではなく

FROM
    depart AS d
  LEFT JOIN
    employee AS e
  ON
    d.depart_id = e.depart_id
WHERE
  e.depart_id IS NULL

2-3

自己結合は同じテーブル同士ということ以外、構文自体は外部結合、内部結合と同じ。
ただし、必ずテーブルに別名をつける必要がある。

自己結合を利用することで、このように再帰的、あるいは、階層構造を持った情報をシンプルなコードで取り出す

そういえば、

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

の最後に階層構造を持ったデータのテーブル設計について書いてあった。

2-4

JOIN句の入れ子自体は実は結構書いたことがあったのだけど、INNER JOINが結合しているテーブルのうち、一番少ないテーブルの列の行数分だけ出力される、みたいに思っているところがあって、

A1 -+--B1--C1
    +--B2--C2
    +--B3--C3

A2 -+--B4--C4
    +--B5--C5

A3 ----B6--C6

というように、間に一対多対応が入っている複数のテーブルの結合だと、なんだかBの列がいなくなりそうでRIGHT JOIN書いちゃう、みたいな誤答をした。

INNER JOINをしたときに出力されるのは、Aの行数分だけじゃなくて、A-Bの間の結合の数だけですね。

つまり、

A B C
A1 B1 C1
A2 B4 C4
A3 B6 C6

じゃなくて

A B C
A1 B1 C1
A1 B2 C2
A1 B3 C3
A2 B4 C4
A2 B5 C5
A3 B6 C6

だ。

2-5

なんだか難しかった。というより、この辺まで来ると一文一文が重い……。
もう個々のキーワードに関してはふまえてきた感があるので、次節からはパソコンで書こう。

HAVING句はGROUP句なしでも文法上は使えないことは無いけど、意味は無い。

select price from receipts HAVING MIN(price) > 1000;
// 全体の中で合致するもののなかで一番最初の行が出力される
  • HAVINGはグループを絞りこむキーワードなので、HAVING句には個別の行を表すtable.columnといった列名は置けない
  • WHEREは全体から行を絞り込むキーワードなので、グループを使う集計関数は置けない

WHERE EXISTSを使うと、表結合と違って、必要分だけ出る。

A B C
A1 B1 C1
A2 B4 C4
A3 B6 C6

さっきの例を使うと、こんなイメージ、うまく説明できない。

WHERE EXISTSを使うとき、副問い合わせのSELECT文で返すのは*。

このように主問い合わせとなるSELECT命令と、サブクエリのSELECT命令とが相関関係を持つ問い合わせのことを相関サブクエリといいます。相関サブクエリでは、主となるテーブルの各行についてサブクエリが実行されることになります。

2-6

複数テーブルから取り出したデータの和集合や差集合を求める。
正直初めて見た。RIGHT JOINやINNER JOINとWHERE句を使って書いたらいいのではないのか、という気もしたが、EXCEPT演算はともかくUNION演算子は思いつかなかった。

例は引用。

  SELECT # 指定列を取り出し
    u.l_name_kana,
    u.f_name_kana
  FROM
    usr AS u
UNION # 上下のSELECT命令の結果を結合(重複はカット)
  SELECT # 指定列を取り出し
    e.l_name_kana,
    e.f_name_kana
  FROM
    employee AS e
ORDER BY
  1,2  #=> 1,2列目の順で昇順ソート
;
  • 列の数とデータ型はテーブル間で等しくする
  • ORDER BY句は最後に記述する
  • UNION演算子を利用した場合のソートキーは番号で指定する
    • テーブル間で同じ意味を表す列の名前が異なる可能性があるので。文法的には普段のソートでも可。
  • UNION ALL演算子(重複を除く処理が無い)方が早い
    • DISTINCT, GROUP BY句などを使った時も暗黙のソート処理が行われる
  • 積集合を求めるためのINTERSECT演算子や、差集合を求めるためのEXCEPT(MINUS)演算子も用意
    • MySQLではEXCEPT, INTERSECT演算子は使えない。
    • CONCAT(a, b)のかわりに a || b