技術日誌

DB,Java,セキュリティ,機械学習など。興味のあることを雑多に学ぶ

プログラマのためのSQL 読書会(20)に参加

25.3.4の自己外部結合から25章最後まで読みました。

自己結合、自己外部結合などがテーマです。 SQLでパズル的なことをやろうとすると自己外部結合が役立ちそうです。 あとは障害調査くらいですかね。

正誤表と掲載SQL

今回読書会中に詰まることがあり必要になりました。どちらも翔泳社の公式サイトにあります。

www.shoeisha.co.jp

ただし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 入門 ドリル215問付き! (スッキリシリーズ)

スッキリわかる SQL 入門 ドリル215問付き! (スッキリシリーズ)

改訂第3版 すらすらと手が動くようになる SQL書き方ドリル (WEB+DB PRESS plus)

改訂第3版 すらすらと手が動くようになる SQL書き方ドリル (WEB+DB PRESS plus)

複数冊ありますがどちらもおすすめとのこと。 後者はブラウザでSQLを試せる環境がついています。

  • キリン本。初学者が躓きやすいgroup byのサポートが充実。また入門書で集約関数まで扱っているものは少ない。
    • 私もSQLの学習はこの本からはじめました。*1

SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

  • 会社がSQLのドリルを作ってる。
  • 林優子さんのSQLドリル(書籍? or 講習?)が良いらしい

どこかで話がそれてマニアックなデータベース本の話題になりました。

  • データベースリファクタリングという本がメチャクチャ良い。
    • 例のピアソンショックでプレミアがついてる
    • 積読している人は質屋に持っていくと金になるかも。。。

データベース・リファクタリング

データベース・リファクタリング

  • 作者: スコット W アンブラー,ピラモド・サダラージ,梅澤真史,越智典子,小黒直樹
  • 出版社/メーカー: ピアソンエデュケーション
  • 発売日: 2008/03/26
  • メディア: 単行本
  • 購入: 10人 クリック: 211回
  • この商品を含むブログ (53件) を見る

  • 川崎の工業図書館?にSQL関連のレアな書籍が結構ある。

    横浜に県立図書館がすでに開館していることと、工業都市・川崎にあることから、開館当初から自然科学・工業・産業分野を重点とした資料収集・サービス方針を掲げている。

  • カーリル*2というサービスで全国の図書館を検索できる

  • 成熟している分野なので教材などは充実している感じですが、定番の問題みたいなのは特になさそうです。

人名

この本は質問サイト?のようなコミュニティの引用も多く、一般人の人名が多く登場します。 主催の木村さんは同名人物っぽい人をlinked inで見つけてメッセージを送ってみたそうですが、返事がなかったようです。

初版が古い本なので、昔から書かれている人とはもう連絡を取るのが難しいかもしれませんね。 最近の本だとtwitterIDを載せると思いますし、昔より連絡コストは大きく減りつつあると思います。

感想

パズル系のクエリは普段描かないような書方で描かれていることが多かったり、クエリそのものが長くなっている等で、SQLにコメントがついていないと理解が難しいです。本章は読書会で読むというより、家でじっくり試しながら読むほうがよさそうです。