SQL素人でも分かるテーブル結合(inner joinとouter join)

はじめに　テーブルの結合とは

DBからデータを取り出す際、「◯◯から☓☓を取り出す」といった選択を行っているかと思います。
そしてこのデータ取り出しの際、複数テーブルからデータを検索して取得するといったケースも多々あるかと思います。

ですが、例えばAとBというテーブルに対して、「Aを調べる⇒Bを調べる⇒２つの検索結果をがっちゃんこ」なんてしてると時間がかかりますよね？
なので通常の検索の場合、「AとBのテーブルをまとめる⇒そこから検索」という方法を取ります。
この「まとめる」ことを「結合」と呼び、その方法として、今から取り扱う内部結合や外部結合などといったものが存在しています。

今回は、犬の名前と飼い主の名前を扱う２つのテーブル(dogsテーブルと、ownerテーブル)を扱いながら、その結合方法に関して追っていければと思います。

dogsテーブル

id	name	owner_id
1	aka	1
2	ao	2
3	shiro	1
4	kuro	4

ownerテーブル

id	name
1	ichiro
2	jiro
3	saburo

それではいきましょう。

内部結合と外部結合

まず、２つのテーブルを結合する方法として、大きく内部結合と外部結合というものが存在しています。

内部結合

内部結合は、それぞれのテーブルの指定したカラムの値が一致するものだけを結合します。
...と言われてもイメージつかないですよね。

まずは基本構文を見ていきましょう。
基本構文は、下記のような形です。

内部結合SQLの基本構文

SELECT カラム名1, カラム名2, ... FROM テーブル名１
  INNER JOIN テーブル名2 ON 結合の条件

この構文の意味合いとしては、「テーブル1からカラム1、カラム2を取ってきた後に、結合の条件に従ってテーブル2からも値を取得し、ひっつけて１つのテーブルにしてくださいね」といったものです。

では、これを頭の片隅に置きながら、具体例を見ていきましょう。

SQL文

    SELECT * FROM dogs
INNER JOIN owners 
        ON dogs.owner_id = owners.id;

内部結合の命令には、inner joinという文を使います。
この場合、「dogsテーブルから全てのカラムを取得し、それに、dogsテーブルのowner_idとownersテーブルのidを対応させた状態で、ownersテーブルをひっつけてあげてください」という命令になります。
つまり、このSQL文の実行結果、こんなテーブルが出来上がります。

id	name	owner_id	id	name
1	aka	1	1	ichiro
3	shiro	1	1	ichiro
2	ao	2	2	jiro

dogsテーブルのkuroがいなくなっていることに注目してください。
kuroのowner_idは4です。ですが、ownersテーブルでidが4番は存在していません。
内部結合の場合、ベースとなるテーブルから、条件にマッチするレコードがないものは削除されるのです。

---

外部結合

外部結合は、内部結合のようにそれぞれのテーブルの指定したカラムの値が一致するものを結合するのに加え、どちらかのテーブルにしか存在しないものに関しても取得します。

基本構文はこちら。

外部結合の基本構文

SELECT カラム名1, カラム名2, ...,FROM テーブル1 
LEFT(RIGHT) OUTER JOIN テーブル名2 ON 結合の条件

LEFT(RIGHT) OUTER JOINなる新出単語が出てきましたね。
このLEFT OUTER JOIN、RIGHT OUTER JOINとは、どちらとも外部結合の方法を定義しています。

• LEFT OUTER JOIN: 左側のテーブルを軸にして外部結合を行う方法
• RIGHT OUTER JOIN:右側のテーブルを軸にして外部結合を行う方法

と今は覚えておいてください。
これから具体例を見ていく中で、よりこの両者がクリアになっていくかと思います。

それでは、具体例を見ていきましょう。

---

LEFT OUTER JOINでの外部結合

SQL文

         SELECT * FROM dogs 
LEFT OUTER JOIN owners 
             ON dogs.owner_id = owners.id;

このようなSQL文が実行されたとしましょう。
left outer joinでは左のテーブルを基準にしましょうとあったので、この場合、基準となるテーブルはdogsテーブルです。
1.dogsテーブルから全件データを取得
2.dogsテーブルのowner_idがownersテーブルのidと合致するものをownersテーブルから取得して、1で取得したdogsテーブルに結合

結果はこうなります。

id	name	owner_id	id	name
1	aka	1	1	ichiro
2	ao	2	2	jiro
3	shiro	1	1	ichiro
4	kuro	4	null	null

注目はやはりkuroです。
内部結合の際は一致するデータが無かったためにテーブルから削除されていたkuroが、外部結合の場合はデータとして残っているのが分かりますね。

そして、左のdogsテーブルが基準となっているため、並び順もdogsテーブルのidがベースになっています。

では、RIGHT OUTER JOINの場合はどうなるのでしょうか？

---

RIGHT OUTER JOINでの外部結合

SQL文

          SELECT * FROM dogs 
RIGHT OUTER JOIN owners 
              ON dogs.owner_id = owners.id;

RIGHT OUTER　JOINなので、右側のテーブル、つまりこの場合はownersテーブルが基準となります。
そして外部結合なので、どちらかのテーブルにしか存在しないものに関しても取得します。
この場合の取得結果は、どうなるのでしょうか？

id	name	owner_id	id	name
1	aka	1	1	ichiro
3	shiro	1	1	ichiro
2	ao	2	2	jiro
null	null	null	3	saburo

このようになりました！
今回の注目はsaburoですね。
右側のownersテーブルが基準となるため、ownersテーブルの全件は漏れなく取得されます。そして、それに対応するデータをdogsテーブルから探した結果、このような形となりました。
並び順がownersテーブルのid順になっている点にも注目してください。

最後に

僕はRails使いなので、このあたりはActiveRecordの恩恵に預かっている部分が大きいです。
ですが、いざ大規模なサービスを扱うとなると、このあたりを知っておかないと適切な条件を書けないことに気付かされ、改めて勉強してみようと思った次第です。
ですので、僕みたいに今はなんとかなっているという方も、これを気に是非SQLの深い部分まで踏み入って勉強してみて下さい。　色々と見えてくる景色が変わってきますよ！