まさ@ブログ書き込み中

自由に生きるための英語、プログラミング、考えごとについて色々書いています。

OUTER JOINの挙動と隣接リストでの使い方【SQLアンチパターン】

f:id:masaincebu:20180609195631p:plain


皆さんこんばんは、まさです。

最近の僕の投稿を見るとわかると思いますが、僕は先月の終わりから今月にかけてDB設計の本を読んでいます。


『楽々ERDレッスン』、『達人に学ぶDB設計』に続いて今日から『SQLアンチパターン』を読み始めました。

SQLアンチパターン

SQLアンチパターン

   

今回は本書で紹介されているテーブル設計のアンチパターンを通して多くのエンジニアが使っているであろうSQL、OUTER JOINの使い方をまとめていきます。

OUTER JOINとは

内部結合の理解は比較的簡単な気がするので外部結合についての説明をしておきます。


OUTER JOINにはLEFT OUTER JOINとRIGHT OUTER JOINがあります。これは指定した左側(もしくは右側)のテーブルのデータは全て残しつつも、もう片方のテーブルではマッチしたレコードはそのまま残し、マッチしていないレコードはNULLとして表示するというものです。


言葉だけで伝えるとわかりづらそうなので、以下のSQLの結果を参考にしてみてください。

f:id:masaincebu:20180609202128p:plain

ここではCommentsテーブル自体を外部結合しています。このテーブルではparent_idを見ればコメントがどのコメントに対して行われたかを表しています。Twitterのコメントのリプライのイメージをしてもらえるとわかりやすいかもしれません。


SQL文にある通り、ここでは各コメントの親子関係を出そうとしています。この結果からわかるのは、comment_idが3, 5, 7のコメントは子コメント(そのコメントに対するコメント)がないことです。


内部結合も含めたわかりやすい説明はこのサイトに詳しいので、もし良かったら後で見てみてください。


SQLのINNER JOINとOUTER JOINの解説をリライトした例:技術屋のためのドキュメント相談所:オルタナティブ・ブログ


ここまででOUTER JOINの挙動が理解できていなくても問題ないです。後で順を追ってまとめていきます。

隣接リスト

僕が読んでいる『SQLアンチパターン』ではDB設計に関するアンチパターン(よくない例)を紹介しています。


そこで出て来た例の一つが「隣接リスト」です。


例えばあるニュースに対してコメントが出来るようなサイトがあったとします。他にもこのニュースに対して別のコメントがあったり、またはコメントにも多数のコメントがあったりしたとします。


まず、安直に考えると最初にニュースに対してコメントをした人のコメントを以下のような情報でテーブルに登録したいと考えるでしょう。

comment_id : 1
comment : このニュースで取り上げられていたまさのブログ面白いよ!


しかし、すぐにこのテーブル設計では困ることがわかります。あるコメントに対するコメントがあった場合、それを示す情報がどこにもないからです。


そんな時によく使われる方法が「隣接リスト」です。文字通り、隣接している関係を情報として保持しておくわけです。ここではコメントする相手、親コメントです。

comment_id : 1
parent_id : NULL
comment : このニュースで取り上げられていたまさのブログ面白いよ!


comment_id : 2
parent_id : 1
comment : ぶっちゃけそうでもない


これでcomment_idが2のコメントは1つ目のコメントに対するコメントだとわかります。

隣接リストの問題点

さて、今回の記事の本題に入ります。この隣接リストの問題点はあるコメントに端を発した全てのコメントを取得するのが難しいということです。

comment_id : 1
parent_id : NULL
comment : このニュースで取り上げられていたまさのブログ面白いよ!


comment_id : 2
parent_id : 1
comment : ぶっちゃけそうでもない


comment_id : 3
parent_id : 1
comment : ためにはなるけど面白くない


comment_id : 4
parent_id : 3
comment : ためにならない時もあるんだよなこれが


この例だと最初のコメントから二つのコメントが生まれて、さらにそのコメントのうち一つのコメントに対してコメントが書かれています。


こういった中で「comment_idが1のコメントから始まった全てのコメントを取ってきたい」という時に隣接リストだとそれが面倒になってしまうのです。


そして、それでもコメントの親子関係をSQLで表そうと出てくるのがOUTER JOINです。

OUTER JOINを使って全ての子孫コメントを取ってくる

さて、まずは以下のようなCommentsテーブルを例として利用します。

f:id:masaincebu:20180609205640p:plain

まさに隣接リストがテーブル設計として採用されていますね。


次に、あるコメントの子コメントを取ってくるためのSQLを考えてみましょう。これは冒頭で紹介したものと同じSQLです。

f:id:masaincebu:20180609202128p:plain

冒頭で「LEFT OUTER JOINをすると左側のテーブルの値はそのまま残る」と言いましたが、1つ目のSQLの結果から見れるテーブル構造と2つ目の画像のSQLの結果の左側は少し異なっています。


例えば、comment_idが1と4のコメントが二つ結果として出力されています。これは、条件文を基にもう一つのテーブルと結合しているためです。comment_idが1と4のコメントには子コメントが二つ居るわけです。


さて、これでやっと隣接リストの特徴を活かして直下の子コメントを取ってこれたのですが、本来の目的は直下の子コメントだけでなく、すべての子孫コメントを取ってくることです。


こうなるととてもややこしくなります。イメージとしては、子孫関係の深さの分だけLEFT OUTER JOINを繰り返し、子コメントの子コメントは何か、その子コメントは・・・と取得する必要があるのです。

f:id:masaincebu:20180609212306p:plain

これは、三階層に渡る子コメントを取って来ている例です。見方としては、あるレコードの横一列が親子関係を表していると思ってください。


これが最後の階層までを含めた親子関係を取得するSQL文です。ちょっと見えづらい・・・。

f:id:masaincebu:20180609212740p:plain

このようにして、隣接リストをテーブル設計として採用してしまうと外部結合が階層の深さの分だけ必要になってきます。これが本書で隣接リストがアンチパターンとして紹介されている理由の一つです。他にも、隣接リストの1つのデータを削除したい場合は外部キーの整合性制約を満たすために最下層から順番に子孫コメントを削除する必要がある・・・など問題点があります。

最後に

それでは今回はここまでにしておきます。アンチパターンだけ教えて解決策をまとめないのもアレですが、あくまで今回は外部結合の挙動について実例を交えながらまとめたかったのでこれ以上は深追いしません。外部結合の挙動について、ついでに隣接リストの問題点についてまとめられていたらいいかな、と思っています。


ではまた。