プログラマのためのSQL 読書会(20)に参加
25.3.4の自己外部結合から25章最後まで読みました。
自己結合、自己外部結合などがテーマです。 SQLでパズル的なことをやろうとすると自己外部結合が役立ちそうです。 あとは障害調査くらいですかね。
正誤表と掲載SQL
今回読書会中に詰まることがあり必要になりました。どちらも翔泳社の公式サイトにあります。
ただしSQLはプレーンテキストが置いてあるだけであり、またSQLを動かすためのinsert文がついていないものも多数あります。また正誤表で上がっているものの訂正もされていないようですので注意が必要です。
外部結合は順番が入れ替えられない
25.3.5の内容。外部結合は結合則が成り立たない
- (A + B) + C
- A + (B + C)
これが同じ結果にならない可能性がある。 どの順番で結合をするかで結果が変わってしまう。
サポートでも外部結合が入っているとお手上げらしい。
25.4のクエリの疑問
---生徒テーブル CREATE TABLE Students (student_nbr INTEGER NOT NULL PRIMARY KEY, student_type CHAR(1) NOT NULL DEFAULT 'D' CHECK (student_type IN ('D', 'F'))); ---国内学生テーブル CREATE TABLE DomesticStudents (student_nbr INTEGER NOT NULL PRIMARY KEY REFERENCES Students(student_nbr)); ---留学生テーブル CREATE TABLE ForeignStudents (student_nbr INTEGER NOT NULL PRIMARY KEY REFERENCES Students(student_nbr)); ---データ投入 -- Students INSERT INTO Students(student_nbr, student_type) VALUES('1', 'D'); INSERT INTO Students(student_nbr, student_type) VALUES('2', 'D'); INSERT INTO Students(student_nbr, student_type) VALUES('3', 'F'); INSERT INTO Students(student_nbr, student_type) VALUES('4', 'D'); INSERT INTO Students(student_nbr, student_type) VALUES('5', 'F'); -- DomesticStudents INSERT INTO DomesticStudents(student_nbr) VALUES('1'); INSERT INTO DomesticStudents(student_nbr) VALUES('2'); INSERT INTO DomesticStudents(student_nbr) VALUES('4'); -- ForeignStudents INSERT INTO ForeignStudents(student_nbr) VALUES('3'); INSERT INTO ForeignStudents(student_nbr) VALUES('5'); SELECT Students.*, DomesticStudents.*, ForeignStudents.* FROM Students LEFT OUTER JOIN DomesticStudents ON CASE Students.student_type WHEN 'D' THEN 1 ELSE NULL END = 1 LEFT OUTER JOIN ForeignStudents ON CASE Students.student_type WHEN 'F' THEN 1 ELSE NULL END = 1 --注意:正誤表を見ると以下が抜けていた。 WHERE ( Students.student_nbr = DomesticStudents.student_nbr OR Students.student_nbr = ForeignStudents.student_nbr);
結果
student_nbr | student_type | student_nbr | student_nbr -------------+--------------+-------------+------------- 1 | D | 1 | 2 | D | 2 | 3 | F | | 3 4 | D | 4 | 5 | F | | 5
なぜCASE式を使っているのか?という意見が出た。
- Students.student_type = D
- Students.student_type = F
の2つの結合で良いのではないかという意見が出た。
SELECT Students.*, DomesticStudents.*, ForeignStudents.* FROM Students LEFT OUTER JOIN DomesticStudents ON Students.student_type = 'D' LEFT OUTER JOIN ForeignStudents ON Students.student_type = 'F' WHERE ( Students.student_nbr = DomesticStudents.student_nbr OR Students.student_nbr = ForeignStudents.student_nbr);
結果
student_nbr | student_type | student_nbr | student_nbr -------------+--------------+-------------+------------- 1 | D | 1 | 2 | D | 2 | 3 | F | | 3 4 | D | 4 | 5 | F | | 5
確かに結果は変わらないですね。
古いSQLの結合構文の有利な点
結合が宣言的にかけるというメリットがある。
-- JOINを使わない古い結合 SELECT x, y, z FROM Foo, Bar, Flub WHERE y BETWEEN x AND w;--結合条件が宣言的にかける -- JOINを使う新しい結合 SELECT x, y, z --Foo,BAR,Flubとインクリメンタルに結合する必要がる。 --SQLで手続き的に書く必要が出てくる。 FROM Foo INNER JOIN Bar ON y >= x INNER JOIN Flub ON y <= w;
- シーター結合は普通はやらない。
- 新しい結合句で問題ない。
新人研修や初学者がとくべき定番の問題
自己結合の章ではパズルのような問題を沢山ときます。そういえばSQLでは
などの他のプログラミング言語だと最初に解いて見る定番の演習問題が無いので 質問してみました。
- SQLドリル
スッキリわかる SQL 入門 ドリル215問付き! (スッキリシリーズ)
- 作者: 中山清喬,飯田理恵子
- 出版社/メーカー: インプレス
- 発売日: 2013/04/19
- メディア: 単行本(ソフトカバー)
- この商品を含むブログ (5件) を見る
改訂第3版 すらすらと手が動くようになる SQL書き方ドリル (WEB+DB PRESS plus)
- 作者: 羽生章洋,和田省二,菅井大輔
- 出版社/メーカー: 技術評論社
- 発売日: 2016/04/12
- メディア: 大型本
- この商品を含むブログを見る
複数冊ありますがどちらもおすすめとのこと。 後者はブラウザでSQLを試せる環境がついています。
SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2016/06/17
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る
どこかで話がそれてマニアックなデータベース本の話題になりました。
- 作者: スコット W アンブラー,ピラモド・サダラージ,梅澤真史,越智典子,小黒直樹
- 出版社/メーカー: ピアソンエデュケーション
- 発売日: 2008/03/26
- メディア: 単行本
- 購入: 10人 クリック: 211回
- この商品を含むブログ (53件) を見る
川崎の工業図書館?にSQL関連のレアな書籍が結構ある。
- ここでしょうか。神奈川県立川崎図書館
横浜に県立図書館がすでに開館していることと、工業都市・川崎にあることから、開館当初から自然科学・工業・産業分野を重点とした資料収集・サービス方針を掲げている。
カーリル*2というサービスで全国の図書館を検索できる
- 意外と近所の図書館にデータベースリファクタリングありました。
成熟している分野なので教材などは充実している感じですが、定番の問題みたいなのは特になさそうです。
人名
この本は質問サイト?のようなコミュニティの引用も多く、一般人の人名が多く登場します。 主催の木村さんは同名人物っぽい人をlinked inで見つけてメッセージを送ってみたそうですが、返事がなかったようです。
初版が古い本なので、昔から書かれている人とはもう連絡を取るのが難しいかもしれませんね。 最近の本だとtwitterIDを載せると思いますし、昔より連絡コストは大きく減りつつあると思います。
感想
パズル系のクエリは普段描かないような書方で描かれていることが多かったり、クエリそのものが長くなっている等で、SQLにコメントがついていないと理解が難しいです。本章は読書会で読むというより、家でじっくり試しながら読むほうがよさそうです。
プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに
- 作者: Joe Celko
- 出版社/メーカー: 翔泳社
- 発売日: 2015/01/19
- メディア: Kindle版
- この商品を含むブログ (11件) を見る