データベース操作において、複数のテーブルから関連するデータを効率的に抽出するための操作として「テーブル結合(JOIN)」があります。今回の記事では、SQLにおけるJOINの基本的な概念と、その具体的な使用方法について詳しく解説します。
JOIN(結合)とは?
JOINとは、異なるテーブル間のデータを関連付けて、一度に複数のテーブルからの情報を取得することができる操作です。JOINは、取得したい情報の性質やデータの取扱いに応じて、5つの種類を使い分けることができます。 まず、これらの5つのJOINの種類を紹介し、続いて具体的なデータを用いてクエリと抽出されるデータについて解説していきます。
クロス結合(CROSS JOIN):テーブルのすべての組み合わせを生成します。
テーブルA | テーブルB |
---|---|
A1 | B1 |
A1 | B2 |
A2 | B1 |
A2 | B2 |
内部結合(INNER JOIN):両方のテーブルに共通するデータのみを取得します。
テーブルA | テーブルB |
---|---|
A1 | B1 |
左外部結合(LEFT JOIN):左テーブルのデータは全て表示し、対応する右テーブルのデータがない場合はNULLとして表示します。
テーブルA | テーブルB |
---|---|
A1 | B1 |
A2 | NULL |
右外部結合(RIGHT JOIN):右テーブルのデータは全て表示し、対応する左テーブルのデータがない場合はNULLとして表示します。
テーブルA | テーブルB |
---|---|
A1 | B1 |
NULL | B2 |
完全外部結合(FULL OUTER JOIN):両方のテーブルの全データを表示し、対応するデータがない場合はNULLとして表示します。
テーブルA | テーブルB |
---|---|
A1 | B1 |
A2 | NULL |
NULL | B2 |
ここから先では、具体的なデータを用いて、それぞれの結合の種類を見ていきます。
あなたはある企業やショップのデータエンジニアとして、広告キャンペーンの効果を分析する役割を担っています。具体的には、顧客の基本情報を持った「A. 顧客情報テーブル(customers)」と、顧客の購入履歴を記録した「B. 購入情報テーブル(purchace_history)」の2つのサンプルテーブルを使用して、データの分析と解析を行います。
A. 顧客情報テーブル(customers)
customer_id | name |
---|---|
1 | 佐藤 |
2 | 鈴木 |
3 | 加藤 |
4 | 山本 |
5 | 伊藤 |
6 | 松田 |
B. 購入情報テーブル(purchace_history)
history_id | customer_id | route | saved_date | price |
---|---|---|---|---|
101 | 1 | ad1 | 2023-04-01 17:29:31 | 2100 |
102 | 2 | ad2 | 2023-04-05 13:10:45 | 3500 |
103 | 3 | ad1 | 2023-04-19 11:37:01 | 1300 |
104 | 4 | ad3 | 2023-05-03 15:59:58 | 4200 |
105 | 5 | ad1 | 2023-05-13 09:11:47 | 5000 |
106 | 6 | ad2 | 2023-05-24 19:49:28 | 2500 |
クロス結合(CROSS JOIN)とは?
クロス結合は、一方のテーブルの各レコードを、もう一方のテーブルのすべてのレコードと組み合わせる結合方法です。AテーブルのすべてのレコードとBテーブルのすべてのレコードの組み合わせを生成するため、結果は、Aのレコード数 × Bのレコード数になります。
ここでは、A, Bともに下3つのレコードを削除した表を用いて見ていきます。
A. 顧客情報テーブル(customers)
customer_id | name |
---|---|
1 | 佐藤 |
2 | 鈴木 |
3 | 加藤 |
B. 購入情報テーブル(purchace_history)
history_id | customer_id | route | saved_date | price |
---|---|---|---|---|
101 | 1 | ad1 | 2023-04-01 17:29:31 | 2100 |
102 | 2 | ad2 | 2023-04-05 13:10:45 | 3500 |
103 | 3 | ad1 | 2023-04-19 11:37:01 | 1300 |
クエリ:
1 2 3 4 5 6 7 8 9 |
SELECT customer.name, history.route, history.saved_date, history.price FROM customers AS customer CROSS JOIN purchase_history AS history; |
SELECT句: 取得するカラムを指定しています。具体的には、Aテーブルのnameカラムと、Bテーブルのroute, saved_date, priceカラムを取得します。
FROM句: 主要なテーブルを指定します。この例では、A. 顧客情報テーブル(customers)を指定し、AS historyでcustomerというエイリアス(別名)で参照しています。
CROSS JOIN句: クロス結合するテーブルを指定します。この例では、B. 購入情報テーブル(purchace_history)を指定し、AS historyでこのテーブルにエイリアスhistoryを付けています。
結果:
name | route | saved_date | price |
---|---|---|---|
佐藤 | ad1 | 2023-04-01 17:29:31 | 2100 |
佐藤 | ad2 | 2023-04-05 13:10:45 | 3500 |
佐藤 | ad1 | 2023-04-19 11:37:01 | 1300 |
鈴木 | ad1 | 2023-04-01 17:29:31 | 2100 |
鈴木 | ad2 | 2023-04-05 13:10:45 | 3500 |
鈴木 | ad1 | 2023-04-19 11:37:01 | 1300 |
加藤 | ad1 | 2023-04-01 17:29:31 | 2100 |
加藤 | ad2 | 2023-04-05 13:10:45 | 3500 |
加藤 | ad1 | 2023-04-19 11:37:01 | 1300 |
このように、クロス結合はAテーブルのレコードがBテーブルの各レコードと結合されるため、結果のテーブルは 3 × 3 = 9 通りとなります。
クロス結合の基本形は、以下のクエリとなります。
1 2 3 4 5 6 |
SELECT テーブル1のカラム, テーブル2のカラム, ... FROM テーブル1 AS テーブル1のエイリアス CROSS JOIN テーブル2 AS テーブル2のエイリアス; |
エイリアスは必須ではありませんが、クエリの可読性を上げるためや、同じテーブルを複数回参照する場合などに便利です。
内部結合(INNER JOIN)とは?
内部結合は、指定された結合条件に基づいて二つのテーブルを結合する方法です。この結合方式では、条件に一致するレコードのみが結果として返されます。具体的には、AテーブルのあるレコードとBテーブルのあるレコードが結合条件を満たす場合、それらのレコードの組み合わせだけが出力されます。
今回、内部結合を分かりやすくするため、A. 顧客情報テーブルにcustomer_id「7」を追加し、B. 購入情報テーブルをcustomer_id「4」⇒「8」、「5」⇒「9」としました。
A. 顧客情報テーブル(customers)
customer_id | name |
---|---|
1 | 佐藤 |
2 | 鈴木 |
3 | 加藤 |
4 | 山本 |
5 | 伊藤 |
6 | 松田 |
7 | 田中 |
B. 購入情報テーブル(purchace_history)
history_id | customer_id | route | saved_date | price |
---|---|---|---|---|
101 | 1 | ad1 | 2023-04-01 17:29:31 | 2100 |
102 | 2 | ad2 | 2023-04-05 13:10:45 | 3500 |
103 | 3 | ad1 | 2023-04-19 11:37:01 | 1300 |
104 | 8 | ad3 | 2023-05-03 15:59:58 | 4200 |
105 | 9 | ad1 | 2023-05-13 09:11:47 | 5000 |
106 | 6 | ad2 | 2023-05-24 19:49:28 | 2500 |
クエリ:
1 2 3 4 5 6 7 8 9 10 11 |
SELECT customer.name, history.route, history.saved_date, history.price FROM customers AS customer JOIN purchace_history AS history ON customer.customer_id = history.customer_id; |
SELECT句: 取得するカラムを指定しています。具体的には、Aテーブルのnameカラムと、Bテーブルのroute, saved_date, priceカラムを取得します。
FROM句: 主要なテーブルを指定します。この例では、A. 顧客情報テーブル(customers)をcustomerというエイリアスで参照しています。
JOIN句: JOIN (またはINNER JOIN)は、別のテーブルを結合するための命令です。この例では、B. 購入履歴テーブル(purchace_histor)をhistoryというエイリアスで結合しています。
ON句: 2つのテーブルを結合するための条件を指定する場所です。この例では、customer.customer_idとhistory.customer_idが一致するレコードを結合しています。
結果:
name | route | saved_date | price |
---|---|---|---|
佐藤 | ad1 | 2023-04-01 17:29:31 | 2100 |
鈴木 | ad2 | 2023-04-05 13:10:45 | 3500 |
加藤 | ad1 | 2023-04-19 11:37:01 | 1300 |
松田 | ad2 | 2023-05-24 19:49:28 | 2500 |
このデータベースの内容を上のクエリで内部結合すると、両方のテーブルに共通して存在しないcustomer_id「7」「8」「9」のレコードは結果に表示されません。以下が結果に表示されなかったデータとなります。
name / customer_id | route | saved_date | price |
---|---|---|---|
田中 | – | – | – |
8 | ad3 | 2023-05-03 15:59:58 | 4200 |
9 | ad1 | 2023-05-13 09:11:47 | 5000 |
内部結合の基本形は、以下のクエリとなります。
1 2 3 4 5 6 7 8 |
SELECT テーブル1のカラム, テーブル2のカラム, ... FROM テーブル1 AS テーブル1のエイリアス JOIN テーブル2 AS テーブル2のエイリアス ON テーブル1のエイリアス.結合キー = テーブル2のエイリアス.結合キー; |
左外部結合(LEFT JOIN)とは?
左外部結合(LEFT JOIN)では、左テーブル(主テーブル)の全てのレコードを表示し、それと関連する右テーブルのレコードがあればその情報を一緒に表示します。関連する右テーブルのレコードがない場合は、その部分はNULLとして表示されます。
ここでは、Bテーブルからcustomer_id「4」と「5」のデータを取り除くと仮定します。すると表は以下のようになります。
A. 顧客情報テーブル(customers)
customer_id | name |
---|---|
1 | 佐藤 |
2 | 鈴木 |
3 | 加藤 |
4 | 山本 |
5 | 伊藤 |
6 | 松田 |
B. 購入情報テーブル(purchace_history)
history_id | customer_id | route | saved_date | price |
---|---|---|---|---|
101 | 1 | ad1 | 2023-04-01 17:29:31 | 2100 |
102 | 2 | ad2 | 2023-04-05 13:10:45 | 3500 |
103 | 3 | ad1 | 2023-04-19 11:37:01 | 1300 |
106 | 6 | ad2 | 2023-05-24 19:49:28 | 2500 |
クエリ:
1 2 3 4 5 6 7 8 9 10 11 |
SELECT customer.name, history.route, history.saved_date, history.price FROM customers AS customer LEFT JOIN purchace_history AS history ON customer.customer_id = history.customer_id; |
SELECT句: 取得するカラムを指定しています。具体的には、Aテーブルのnameカラムと、Bテーブルのroute, saved_date, priceカラムを取得します。
FROM句: 主要なテーブルを指定します。この例では、A. 顧客情報テーブル(customers)をcustomerというエイリアスで参照しています。
LEFT JOIN句: 主テーブル(この場合はAテーブル)の全てのレコードを取得し、第二のテーブル(この場合はBテーブル)のマッチするレコードを結合するための命令です。マッチするレコードが存在しない場合は、第二のテーブルのカラムにはNULL値が入ります。
ON句: 2つのテーブルを結合するための条件を指定する場所です。この例では、customer.customer_idとhistory.customer_idが一致するレコードを結合しています。
結果:
name | route | saved_date | price |
---|---|---|---|
佐藤 | ad1 | 2023-04-01 17:29:31 | 2100 |
鈴木 | ad2 | 2023-04-05 13:10:45 | 3500 |
加藤 | ad1 | 2023-04-19 11:37:01 | 1300 |
山本 | NULL | NULL | NULL |
伊藤 | NULL | NULL | NULL |
松田 | ad2 | 2023-05-24 19:49:28 | 2500 |
このように、Bテーブルにcustomer_id「4」の山本、customer_id「5」の伊藤のデータが入っていないため、NULLが返ってきます。
左外部結合の基本形は、以下のクエリとなります。
1 2 3 4 5 6 7 8 9 |
SELECT テーブル1のカラム, テーブル2のカラム, ... FROM テーブル1 AS テーブル1のエイリアス LEFT JOIN テーブル2 AS テーブル2のエイリアス ON テーブル1のエイリアス.結合キー = テーブル2のエイリアス.結合キー; |
右外部結合(RIGHT JOIN) とは?
右外部結合(RIGHT JOIN)では、右テーブル(参照テーブル)の全てのレコードを基準にして表示し、左テーブルとの関連するデータがある場合その情報を合わせて表示します。関連する左テーブルのデータがない場合、その部分はNULLとして表示されます。
以下のクエリでは、Bテーブルにcustomer_id「7」を追加します。
A. 顧客情報テーブル(customers)
customer_id | name |
---|---|
1 | 佐藤 |
2 | 鈴木 |
3 | 加藤 |
4 | 山本 |
5 | 伊藤 |
6 | 松田 |
B. 購入情報テーブル(purchace_history)
history_id | customer_id | route | saved_date | price |
---|---|---|---|---|
101 | 1 | ad1 | 2023-04-01 17:29:31 | 2100 |
102 | 2 | ad2 | 2023-04-05 13:10:45 | 3500 |
103 | 3 | ad1 | 2023-04-19 11:37:01 | 1300 |
104 | 4 | ad3 | 2023-05-03 15:59:58 | 4200 |
105 | 5 | ad1 | 2023-05-13 09:11:47 | 5000 |
106 | 6 | ad2 | 2023-05-24 19:49:28 | 2500 |
107 | 7 | ad4 | 2023-06-05 10:20:30 | 3100 |
クエリ:
1 2 3 4 5 6 7 8 9 10 11 |
SELECT customer.name, history.route, history.saved_date, history.price FROM customers AS customer RIGHT JOIN purchace_history AS history ON customer.customer_id = history.customer_id; |
SELECT句: 取得するカラムを指定しています。具体的には、Aテーブルのnameカラムと、Bテーブルのroute, saved_date, priceカラムを取得します。
FROM句: この部分では、主要なテーブルを指定します。この例では、A. 顧客情報テーブル(customers)テーブルをcustomerというエイリアスで参照しています。
RIGHT JOIN句: 第二のテーブル(Bテーブル)の全てのレコードを取得し、主要なテーブル(Aテーブル)のマッチするレコードを結合するための命令です。マッチするレコードが存在しない場合は、主要なテーブルのカラムにはNULL値が入ります。
ON句: これは、2つのテーブルを結合するための条件を指定する場所です。この例では、customer.customer_idとhistory.customer_idが一致するレコードを結合しています。
結果:
name | route | saved_date | price |
---|---|---|---|
佐藤 | ad1 | 2023-04-01 17:29:31 | 2100 |
鈴木 | ad2 | 2023-04-05 13:10:45 | 3500 |
加藤 | ad1 | 2023-04-19 11:37:01 | 1300 |
山本 | ad3 | 2023-05-03 15:59:58 | 4200 |
伊藤 | ad1 | 2023-05-13 09:11:47 | 5000 |
松田 | ad2 | 2023-05-24 19:49:28 | 2500 |
NULL | ad4 | 2023-06-05 10:20:30 | 3100 |
こちらの結果では、Aテーブルにcustomer_id「7」のデータが存在しないため、name欄がNULLとなっています。
右外部結合の基本形は、以下のクエリとなります。
1 2 3 4 5 6 7 8 |
SELECT テーブル1のカラム, テーブル2のカラム, ... FROM テーブル1 AS テーブル1のエイリアス RIGHT JOIN テーブル2 AS テーブル2のエイリアス ON テーブル1のエイリアス.結合キー = テーブル2のエイリアス.結合キー; |
外部結合 (FULL OUTER JOIN)とは?
外部結合 (FULL OUTER JOIN) は、左テーブル(LEFT JOIN)と右テーブル(RIGHT JOIN)の両方の結果を組み合わせて、両方のテーブルに存在するすべての行を返します。結合条件にマッチしない行も結果に含まれ、マッチするデータが存在しない場所にはNULLが表示されます。
以下のクエリでは、customers テーブルに新しいcustomer_idを持つ顧客を追加し、purchace_history テーブルにcustomer_id「7」という存在しないIDを持つ購入履歴を追加します。
A. 顧客情報テーブル(customers)
customer_id | name |
---|---|
1 | 佐藤 |
2 | 鈴木 |
3 | 加藤 |
4 | 山本 |
5 | 伊藤 |
6 | 松田 |
7 | 渡辺 |
B. 購入情報テーブル(purchace_history)
history_id | customer_id | route | saved_date | price |
---|---|---|---|---|
101 | 1 | ad1 | 2023-04-01 17:29:31 | 2100 |
102 | 2 | ad2 | 2023-04-05 13:10:45 | 3500 |
103 | 3 | ad1 | 2023-04-19 11:37:01 | 1300 |
104 | 4 | ad3 | 2023-05-03 15:59:58 | 4200 |
105 | 5 | ad1 | 2023-05-13 09:11:47 | 5000 |
106 | 6 | ad2 | 2023-05-24 19:49:28 | 2500 |
107 | 8 | ad4 | 2023-06-05 10:20:30 | 3100 |
クエリ:
1 2 3 4 5 6 7 8 9 10 11 |
SELECT customer.name, history.route, history.saved_date, history.price FROM customers AS customer FULL OUTER JOIN purchace_history AS history ON customer.customer_id = history.customer_id; |
クエリの解説:
SELECT句: 取得するカラムを指定しています。具体的には、Aテーブルのnameカラムと、Bテーブルのroute, saved_date, priceカラムを取得します。
FROM句: この部分で主要なテーブルを指定します。この例では、A. 顧客情報テーブル(customers)をcustomerというエイリアスで参照しています。
FULL OUTER JOIN句: 両方のテーブルの全てのレコードを取得する命令です。片方のテーブルに対応するデータがもう一方のテーブルにない場合、該当するカラムにはNULL値が入ります。
ON句: これは、2つのテーブルを結合するための条件を指定する場所です。この例では、customer.customer_idとhistory.customer_idが一致するレコードを結合しています。
結果:
name | route | saved_date | price |
---|---|---|---|
佐藤 | ad1 | 2023-04-01 17:29:31 | 2100 |
鈴木 | ad2 | 2023-04-05 13:10:45 | 3500 |
加藤 | ad1 | 2023-04-19 11:37:01 | 1300 |
山本 | ad3 | 2023-05-03 15:59:58 | 4200 |
伊藤 | ad1 | 2023-05-13 09:11:47 | 5000 |
松田 | ad2 | 2023-05-24 19:49:28 | 2500 |
渡辺 | NULL | NULL | NULL |
NULL | ad4 | 2023-06-05 10:20:30 | 3100 |
この結果から、customer_id「7」の渡辺の購入履歴が存在しないため、route、saved_date、priceがNULLであること、また、customer_id「8」の購入履歴にはマッチする顧客情報が存在しないため、nameがNULLであることがわかります。
外部結合の基本形は、以下のクエリとなります。
1 2 3 4 5 6 7 8 |
SELECT テーブル1のカラム, テーブル2のカラム, ... FROM テーブル1 AS テーブル1のエイリアス FULL OUTER JOIN テーブル2 AS テーブル2のエイリアス ON テーブル1のエイリアス.結合キー = テーブル2のエイリアス.結合キー; |
まとめ
本記事では、データベースにおけるJOINの役割とその5つの主要な種類について詳しく解説しました。
その他、SQLの様々な特徴や使用方法について例を交えて説明している記事もございますので、ぜひ参考にしてみてください。
SQLの勉強方法は?
SQLには、今回紹介したORDRE BY以外にも多くの構文があり、完全に習得し、自在にデータを扱えるようになるには多くの学習時間と実践の時間が必要になります。
特に、実践の時間については独学ではなかなか設けることができないものです。実践を交えてSQLを身につけたいと考えるのであれば、総合的な実践まで経験できるプログラミングスクールがおすすめです。
当サイトTECH MANIAでは、最短3か月で基礎から実践力・応用力まで身につけることのできるプログラミングスクールを開講しています。
<テックマニアの特徴>
・たしかな育成実績と親身な教育 ~セカンドキャリアを全力支援~
・講師が現役エンジニア ~“本当”の開発ノウハウを直に学べる~
・専属講師が学習を徹底サポート ~「わからない」を徹底解決~
・実務ベースでスキルを習得 ~実践的な凝縮カリキュラム~
独学でつまずいた方、一流のITエンジニアとしてスキルを高めていきたい方は必見です。
お問い合わせは以下のリンクからお進みください。