複数テーブルを扱う ①結合の種類について

テーブルの結合とは

データベースでは通常、情報を複数のテーブルに分けて管理します。例えば、顧客情報は「顧客テーブル」、注文情報は「注文テーブル」といったように、それぞれの情報が別々のテーブルに保存されています。

ですが、
「この情報とあの情報を一緒に使いたいのに、テーブルが分かれている…」
と思ったことはありませんか？

Magiqでは、別テーブルのデータを取り出したり、別テーブルのデータを条件に加えたい時に、サブクエリを用います。サブクエリを用いれば、複数テーブルのデータを組み合わせてデータを抽出することができます。

サブクエリを使うためには、結合をする必要があります。結合とは、複数テーブルを組み合わせて1つのテーブルのように扱う、というイメージです。
結合にはいくつかのタイプがありますので、この記事で解説いたします。

①INNER JOIN

INNER JOIN(内部結合)は、２つのテーブルの共通のデータのみを取り出す結合方法です。
共通のデータがない行については結合が行われません。

以下の２つの社員情報のテーブルを結合することを例に考えてみましょう。
table1は社員IDと名前の一覧、table2は社員IDとその社員の所属部署の一覧になっています。
この2テーブルを、IDを共通項としてINNER JOINするとどうなるでしょうか。

1	鈴木	1	営業
２	佐藤	2	総務

上の表のように、２つの表に共通してあるID1,2のデータのみが抽出されます。一方、ID3の田中さん、4の山本さんのデータはtable1にしかありませんし、ID5の経理の方のデータはtable2にしかありません。こうしたデータは抽出されないというわけです。

②LEFT OUTER JOIN

LEFT OUTER JOIN(左側結合)は、左側のすべてのデータを保持し、右側データとの共通項を結合します。共通のデータがない場合は、右側のテーブルのデータはNULLで埋められます。
説明だけではイメージしにくいので、早速例で見てみましょう。
先ほどと同じ2つのテーブルをLEFT OUTER JOINで結合すると、こうなります。

1	鈴木	1	営業
2	佐藤	2	総務
3	田中	3	null
4	山本	4	null

左側のtable1のデータはすべてそのまま保持されています。右側のtable2に関しては、table1との共通項、ID1,2の所属部署の値が抽出されています。
ID3.4のデータは右側にはありませんが、左側に合わせて表示され、SECTIONのデータは存在しないため“NULL”となっています。

③RIGHT OUTER JOIN

続いて、RIGHT OUTER JOIN(右側結合)です。
こちらは先ほどの②LEFT OUTER JOINと左右を入れ替えただけです。
つまり、右側のデータをすべて保持し、左側データとの共通項を結合します。共通のデータがない場合は、左側のテーブルのデータはNULLで埋められます。
例も見ておきましょう。結合結果がどのようになるか予想してみてください。

1	鈴木	1	営業
2	佐藤	2	総務
5	null	5	経理

右側のtable2のデータはすべて保持されたまま、左側のtable1に関してはtable2との共通項であるID1.2が取り出されています。ID5のデータはtable1にはありませんので、nameの欄は“NULL”となっています。

④FULL OUTER JOIN

FULL OUTER JOIN(完全外部結合)は、両側のテーブルの全てのデータを保持し結合するものです。共通のデータがない場合は、該当するテーブルのデータはNULLで埋められます。
例で見てみましょう。

1	鈴木	1	営業
2	佐藤	2	総務
3	田中	3	NULL
4	山本	4	NULL
5	NULL	5	経理

右側、左側のデータがどちらも保持されています。データが存在しないところは、“NULL”で埋められています。

⑤CROSS JOIN

CROSS JOINは、今まで見てきた4つの結合とは少し性格が異なっています。

今までの結合はIDという共通項を必要としていたと思いますが、この結合には共通項が必要ありません。

CROSS JOINは、左テーブルと右テーブルの全ての行の組み合わせを取得する結合方法です。つまり、結合後のレコード数は、(左側テーブルのレコード数)×(右側テーブルのレコード数)となります。

先ほどから登場している２つの表でCROSS JOINを行うと、どうなるでしょうか。

1	鈴木	1	営業
1	鈴木	2	総務
1	鈴木	5	経理
2	佐藤	1	営業
2	佐藤	2	総務
2	佐藤	5	経理
3	田中	1	営業
3	田中	2	総務
3	田中	5	経理
4	山本	1	営業
4	山本	2	総務
4	山本	5	経理

急に行数が増えましたね。table1の1行目「1_鈴木」のデータに対してtable2の3行のデータが１行ずつ組み合わせられています。table1の2.3.4行目についても同様です。
このように、総当たりを行うCROSS JOINは、結果テーブルのデータ数が非常に大きくなる可能性があるため、注意が必要です。必要な部分のデータのみを抽出するなどの工夫をするようにしましょう。

3つ以上のテーブルを結合したいときは

JOIN句によってデータテーブルの結合が行えるのは、２つのテーブル間のみです。
3つ以上のテーブルを結合したいときは、初めに2つのテーブルを結合させてから、3つ目のテーブルを結合する必要があります。

サブクエリの作成方法

ここまで、結合の種類について説明しました。
例題に入る前に、Magiq上でサブクエリを作成して複数テーブルを扱う方法について確認しておきましょう。

サブクエリは、クエリビルダの「メイン」タブ右のボタンから追加することができます。削除したいときは、サブクエリの右下に削除ボタンがあります。

Magiq上で2つのテーブルを結合したいときは、サブクエリに結合したいテーブルのデータを設定します。

サブ1のタブでtable1から抽出したいデータを指定したサブクエリ1を設定、サブ2のタブでtable2から抽出したいデータを指定したサブクエリ2を設定、という要領です。
後で結合しやすいよう、別名を付けておきましょう。
※デフォルト名は“sub1”,“sub2”...となっています。変更可能です。
sub1が上で説明したところの左側のテーブル、sub2が右側のテーブルになります。

2つのサブクエリが作成出来たら、メインのタブでサブクエリ1とサブクエリ2を結合します。
「主テーブル」の欄に(サブクエリ1の別名)、「サブテーブル」の欄に(サブクエリ2の別名)を入力し、結合種類を選択します。

右のONのところは、上で説明した①～④の結合種類(INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN)で出てきたように、共通項を設定します。上の説明の例でいえば、社員IDが共通項です。

ON　(サブクエリ1の別名.共通項のカラム名)＝(サブクエリ2の別名.共通項のカラム名)

という式になるように設定します。先ほどまでの例であれば、

ON　table1.ID　＝　table2.ID

と設定することになります。

サブクエリの作成方法

テーブルの結合　例題

2022/10月と2022/11月の両方で売り上げがある会員IDを抽出する

【手順】

サブ１で10月に売り上げがある会員ID、サブ2で11月に売り上げがある会員IDを抽出するサブクエリを作成して、メインタブで2つを結合します。
両方で売り上げがある会員IDを抽出したいので、使うのは共通データのみを結合するINNER JOINです。共通項は、会員IDになります。

①サブ１で10月に売り上げがある会員IDを抽出する
「メイン」タブの横の＋ボタンから、サブ1タブを追加します。
注文テーブルから10月に注文をした会員IDを抽出できるように条件設定を行います。
このとき、一人の人の売り上げが複数ある場合IDが重複して出てきてしまうので、DISTINCTにチェックを入れます。
サブ1にはデフォルトで“sub1”という別名がつけられていますが、上の「サブクエリ名」というところで変更することもできます。サブ2においても同様です。

②サブ2で11月に売り上げがある会員IDを抽出する
＋ボタンから、サブ2タブを追加します。
①の手順と同様に、11月に注文をした会員IDを抽出できるように条件を設定します。このとき、DISTINCTを忘れないようにしてください。

③メインタブに戻り、結合する
メインタブに戻り、結合を行います。
主テーブルのところにサブ1の別名“sub1”を、サブテーブルのところにサブ2の別名“sub2”を入力し、結合の種類を選択します。(デフォルトでは、INNER JOINが選択されています。)
また、共通項も設定する必要があります。
ON (10月に売り上げがある会員ID)=(11月に売り上げがある会員ID)
となるようにします。
取り出したいフィールドを選択して、設定完了です。

複数テーブルを扱う ②集合演算子UNION,INTERSECT,EXCEPTの使い方