読者です 読者をやめる 読者になる 読者になる

give IT a try

プログラミング、リモートワーク、田舎暮らし、音楽、etc.

SQLとの付き合い方

SQL

はじめに

先日、会社のメンバーから「SQLのJOIN文を視覚的に理解する」というサイトを紹介されました。


SQLのJOIN文を視覚的に理解する | IDEA*IDEA


おいらも見てみたのですが、「自分がSQLを書いたり読んだりする時はこんなベン図を思い浮かべることはまずないなあ」というのが正直な感想でした。


そこで、おいらがSQLを書いたり読んだりする時の思考過程をアウトプットするとどうなるだろうかと考えてみました。

SQLを書く場合

おいらの場合、以下のような手順でSQLを組み立てています。

1. ルートとなるテーブル(出力のメインデータ)を決める。

まずはベースとなるSQLを書き始めます。
テーブルによっては大量のデータを抱え込んでいる可能性がありますので、とりあえず件数だけを確認するようにします。

SELECT count(*) 
-- 今回は書籍注文情報をメインデータとする
FROM BookOrder bo 
2. ルートのテーブルのフィルター条件をWHERE句に書く。

このとき、数万件以上のデータが存在するような大きなテーブルであれば、フィルター条件に使うカラムにINDEXが作成されていることを確認します。

SELECT count(*) 
FROM BookOrder bo
-- 購入金額が1万円以上の注文を対象とする 
WHERE bo.Payment >= 10000
3. [DEBUG] SQLを実行し、1件以上のデータが存在することを確認する。

この時点でレスポンスが極端に遅かったりすると、INDEXが使われていない可能性が高いです。

4. そのテーブルに存在しないデータがどのテーブルにあるか探す。

ER図が用意されていればER図も参考にしてみましょう*1

5. ルートのテーブルと接続先のテーブルに、結合条件のキーとなるカラムが存在することを確認する。

このとき、接続先テーブルのカラムにINDEXが作成されていることを確認します(たいていの場合、主キーとなるカラムが含まれるはずです)。

6. 接続先のテーブルにデータが存在しないケースがあるか確認する。

このとき、テーブル間のデータ異常は考慮しません。
正常なデータしか存在していないという仮定で、データが存在しないケースがあるかどうかを確認します。
「99%ありえないけど、もしかしたら」なんて考えだすと、全部外部結合になってしまいます。

7. データが存在しないケースがあればLEFT OUTER JOIN(外部結合)を使う。それ以外はINNER JOIN(等結合)を使う。

経験的にいってINNER JOINを使うケースが8割以上だと思います。

SELECT count(*) 
FROM BookOrder bo
-- 注文者がいない注文はありえないのでINNER JOIN
INNER JOIN Customer c ON
c.CustomerId = bo.CustomerId 
-- 入金はまだ完了していない場合もありえるのでLEFT OUTER JOIN
LEFT OUTER JOIN MoneyReceipt mp ON
mp.OrderId = bo.OrderId
WHERE bo.Payment >= 10000
8. [DEBUG] 結合先のデータが正しく取得できていることを確認する。

レスポンスが極端に落ちたり、返却される行数が異様に増減したりした場合はテーブルの結合条件が間違っている可能性が高いので、SQLを見直します。

9. 必要なテーブルを一通り結合し終わるまで、上記4〜8の手順を繰り返す。

一気に複数のテーブルを結合すると問題がおきやすいので、一つずつ確認していきましょう。

10. 必要とされるカラムをSELECT句に指定する。

ここからは仕上げの段階です。
SELECT句を正しく指定します。

SELECT bo.OrderId     -- 注文番号
      ,bo.OrderDate   -- 注文日
      ,bo.Payment     -- 購入金額
      ,c.CustomerName -- 注文者
      ,mp.ReceiptDate -- 入金日(未入金ならNULL)
FROM BookOrder bo
INNER JOIN Customer c ON
c.CustomerId = bo.CustomerId 
LEFT OUTER JOIN MoneyReceipt mp ON
mp.OrderId = bo.OrderId
WHERE bo.Payment >= 10000
11. [DEBUG] 取得結果を確認する。

見た目的におかしい点などがあれば手直しします。

12. 行の並び順をORDER BY句に指定する。

このとき、並び順が毎回一意になることが保証できるような条件を考えます。
そうでなければ、データベース側の都合である日突然並び順が変わってしまうことがありえるからです。

SELECT bo.OrderId 
      ,bo.OrderDate  
      ,bo.Payment     
      ,c.CustomerName
      ,mp.ReceiptDate 
FROM BookOrder bo
INNER JOIN Customer c ON
c.CustomerId = bo.CustomerId 
LEFT OUTER JOIN MoneyReceipt mp ON
mp.OrderId = bo.OrderId
WHERE bo.Payment >= 10000
ORDER BY bo.OrderDate   -- 基本は注文日順
        ,c.CustomerName -- 注文日が同じなら注文者順
        ,bo.OrderId     -- 同じ日に同じ人が注文していたら注文番号順
13. [DEBUG] 取得結果を確認する。

これで終わりです。
こんな感じで作っていくので、頭の中であのようなベン図を思い浮かべることはありません。

INNER JOINとLEFT OUTER JOINが使えればOK

JOINについてはINNER JOINとLEFT OUTER JOINしか使いません。
既存のSQLでもこれ以外は見たことがありません。



RIGHT OUTER JOINは考え方がややこしくなるので、LEFT OUTERに統一しています。
CROSS JOINは特殊なSQLで一回だけ使ったことがありますが、それっきりです。
FULL OUTER JOINは一度も使ったことがないです。



というわけで、INNER JOINとLEFT OUTER JOINの二つだけ抑えておけば、まず大丈夫だと思います。

SQLを読む場合

逆に他人が書いたSQLを解析する時は簡易的なER図を紙の上に書きます。
たとえば上の例で挙げたSQLが他の開発者によって書かれていたら、おいらは下のような図を書いて視覚的に理解します。


f:id:JunichiIto:20110325075858p:image:w400


実際はラフなメモ書きなので、正式な手順があるわけではありませんが、あえて書くとすればこんな感じです。

  1. テーブルを丸で表す。
  2. 結合されているテーブル同士を線で結ぶ。
  3. 結合条件で使われているカラムを線の近くに書く。
  4. 外部結合の場合はそれと分かるような印をつける。(ここでは「0 or 1」と書いた)
  5. データのフィルタリング条件(WHERE句)があればテーブルから線を伸ばしてメモを入れる。

SQLを日本語にする

あと、大事なのはSQLを書く時も読む時もそのSQLを日本語に変換できるかどうかです。
たとえば上の例で挙げたSQLであれば、以下のような日本語に読みかえることができます。


購入金額が1万円以上の書籍注文情報を取得する。


う〜ん、これではちょっと単純すぎるので、条件を一つ追加してみましょう。

SELECT (省略)
FROM BookOrder bo
INNER JOIN Customer c ON
c.CustomerId = bo.CustomerId 
LEFT OUTER JOIN MoneyReceipt mp ON
mp.OrderId = bo.OrderId
WHERE bo.Payment >= 10000
AND   c.BlackListFlag <> 1 -- 追加した条件


ダメな仕様書なんかにはこんな説明が書いてあったりします。


購入金額が1万円以上かつ、ブラックリストフラグが1以外の書籍注文情報を取得する。


「ブラックリストフラグが1」というのはコンピュータ向けの文章です。人間向けではありません。
人間向けの文章として書くならば、こんな感じになるはずです。


購入金額が1万円以上の書籍注文情報を取得する。
ただしブラックリスト扱いとなっている注文者からの注文は除外する。


自分がSQLの仕様を説明したり理解したりする時は、そのSQLを人間向けの文章に変換できているかどうかを確かめてください。
変換できないのであれば、SQLの仕様を十分に理解できていません。

まとめ

実際の業務ではここで挙げたような単純なSQL登場することはほとんどありません。
結合されるテーブルや抽出条件が山ほどあったり、見慣れない演算子や関数がいっぱい登場したりします。


しかし、何事も基本ができていれば応用は何とかなるものです。
SQLが苦手な方もまずはコツコツと基本を習得することから始めてみましょう。

参考文献

おいらの場合、SQLに関しては現場で叩き上げた感じが強いので、オライリーの「詳説 正規表現」みたいに、「SQLをマスターしたいならコレ!」という一冊がなかなか選べません。


決して初心者向きではないのですが、ちゃんと読めば血となり肉となるような参考文献を紹介しておきます。


アート・オブ・SQL ―パフォーマンスを引き出すSQLプログラミング手法 (Theory in practice)

アート・オブ・SQL ―パフォーマンスを引き出すSQLプログラミング手法 (Theory in practice)


中級者から上級者向けの考え方やテクニックが色々と載っています。



SQLの本ではなく、「リレーショナル理論」の本です。
しかも作者はSQLが嫌いなので、SQLではなく「Tutorial D」という超マイナーな言語が使われています。
数学的な集合理論や公式も山ほど出てくる非常に難解な一冊です。
読むのは大変ですが、そのかわりにテーブルやSQLといった「今までの常識」が、実はリレーショナル理論の本来のパワーを制限していたという驚きの事実を明らかにしてくれます。

*1:というかER図は必ず存在すべきだと思いますが。。。