SQL入門
テーブルを結合して取得(INNER JOIN,OUTER JOIN)

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
最終更新日:2019-12-17

テーブルを結合して取得(INNER JOIN,OUTER JOIN)


エクセルVBAでデータベースを扱うためのSQL入門です。
前回データベースの正規化を説明し、マスターデータとトランザクションデータとしてそれぞれのテーブルを作成しました。
分割されたデータは結合しなければ必要な情報がそろいません。
複数テーブルの情報を必要に応じて結合して取得できるようになると、よりデータベースおよびSQLの利便性が感じられるはずです。


エクセルでは、SUMIF,COUNTIF,VLOOKUP,INDEX,OFFSET等々、これらの関数で他シートの情報を取得できます。
これらは、元表に対して情報を追加するものになりますが、SQLではより高度に複数の表を合体させる事が出来ます。
そして、その合体させる方法がSQLには複数用意されています。

使用するテーブル定義は以下になります。

全テーブル定義とテーブル自動作成VBA
データベースの正規化とマスタの作成で作成した全テーブル定義と、テーブル名変更と列追加(ALTERTABLE)とテーブル自動作成で作成したテーブル自動作成VBAを掲載しておきます。エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。

テーブルの結合方法

2つのテーブルを結合して1つのデータにする場合は、良く使う代表的な結合方法として3通りあります。

VBA マクロ SQL JOIN

内部結合:INNER JOIN
両方のテーブルに結合条件を満たす行があるデータのみになります。
どちらか一方にでも結合条件を満たす行が無ければ、そのデータは対象外になります。

結合後
VBA マクロ SQL JOIN

左外部結合:LEFT OUTER JOIN
左のテーブルを優先として、
左のテーブルの全ての行に対して、右のテーブルは結合条件を満たす行だけになります。

結合後
VBA マクロ SQL JOIN

右外部結合:RIGHT OUTER JOIN
右のテーブルを優先として、
右のテーブルの全ての行に対して、左のテーブルは結合条件を満たす行だけになります。

結合後
VBA マクロ SQL JOIN

その他の結合方法
上記3通り以外の結合方法としては、以下のような結合方法もあります。

完全外部結合:FULL OUTER JOIN
両方のテーブルを優先として、結合条件の一致/不一致にかかわらず、両方のテーブルの全ての行が対象となります。
SQLiteではサポートされていません。

交差結合:CROSS JOIN
2つのテーブルの全ての組み合わせを取得する結合方法です。
2つのテーブルの直積(デカルト積)になります。
取得されるデータ量が極めて多くなるので、使用範囲はかなり限定されます。

自然結合:NATURAL
NATURALキーワードを指定することで、結合条件としてそれぞれのテーブルで同じ名前のカラムが自動的に使用されます。
NATURAL INNER JOIN
NATURAL LEFT JOIN
結合条件は省略できますが、
実際には、テーブル定義時にカラム名をかなり入念に検討しなければ使えないでしょう。
何よりSQLだけでは判断できないので、使用する機会はほとんどないと思います。


列名修飾とエイリアス

列名修飾
1つのSQL文に2つ以上のテーブルを指定する場合には、
カラム名が重複してしまいどのテーブルのカラムかが一意に決まらない場合が出てきます。
そこで、どのテーブルのカラムかを指定する修飾子が必要になってきます。

カラム名に対してテーブルを指定する場合は、
テーブル名.カラム名
このように、.(ドット、ピリオド)を使ってテーブルを指定します。

列名が一意に決まらない場合、SQLはエラーとなります。

複数のテーブルにおいて重複していないカラム名はテーブル修飾は必要ありません。
しかし、SQLを見た時、どのテーブルの列か判断できなくなってしまうのでテーブル修飾は必ず行うようにします。

エイリアス
列名(カラム名)には別名(エイリアス)を付けられることは既に紹介しました。

列名 [AS] 別名
任意の式 [AS] 別名

同様に、テーブル名にも別名(エイリアス)を付けられます。

テーブル名 [AS] 別名
任意の式 [AS] 別名

※[AS]を記述できない
※ASを記述できないデータベースもあります。
※エイリアスの大文字小文字は統一してください、大文字小文字が違うとエラーになる場合があります。

列名修飾とエイリアスの使用例
SELECT T.code AS コード FROM t_sales AS T
t_salesの別名にTを指定し、そのT.でカラム名を修飾しています。

内部結合:INNER JOIN

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

テーブル名1とテーブル名2を結合条件で結合し、
条件が一致したデータのみ抽出します。
結合条件には、ANDやORを使った論理式を記述できます。

INNER JOIN の使用例
SELECT T.code,M.name
 FROM t_sales T
 INNER JOIN m_customer M
 ON T.code = M.code
両方のテーブルのcodeカラムが一致している行のみが対象となります。

左外部結合:LEFT OUTER JOIN

SELECT テーブル名.カラム名, ・・・
FROM テーブル名1
LEFT [OUTER] JOIN テーブル名2
ON 結合条件

[OUTER]は省略できます。
テーブル名1とテーブル名2を結合条件で結合し、

結合条件に合致するテーブル名2の存在有無に関わらず、
テーブル名1の全行が対象になります。

LEFT OUTER JOIN の使用例
SELECT T.code,M.name
 FROM t_sales T
 LEFT JOIN m_customer M
 ON T.code = M.code
テーブル名1は常に全行が対象となります。
テーブル名2の合致するデータがない行では、テーブル名2のフィールドはNULLになります。

右外部結合:RIGHT OUTER JOIN

SELECT テーブル名.カラム名, ・・・
FROM テーブル名1
RIGHT [OUTER] JOIN テーブル名2
ON 結合条件

[OUTER]は省略できます。
テーブル名1とテーブル名2を結合条件で結合し、
結合条件に合致するテーブル名1の存在有無に関わらず、
テーブル名2の全行が対象になります。

RIGHT OUTER JOIN の使用例
SELECT T.code,IFNULL(M.name,'マスタなし')
 FROM t_sales T
 RIGHT JOIN m_customer M
 ON T.code = M.code
テーブル名2は常に全行が対象となります。
テーブル名1の合致するデータがない行では、テーブル名1のフィールドはNULLになります。

LEFT JOINとRIGHT JOINは、単に左右が入れ替わっただけの違いになります。
したがって、どちらか一方のみの使用に統一すべきです。
RIGHT JOINは使わずに、LEFT JOINで統一するのが一般的になります。
SQL入門では、LEFT JOINに統一して使用していきます。

外部結合(OUTER JOIN)で結合条件に一致する行が無い場合

LEFT JOIN(またはRIGHT JOIN)で結合した場合、
結合したテーブルに結合条件の行が存在しない場合、フィールドはNULLになります。

:結合後
VBA マクロ SQL JOIN

SELECT T.code,M.name
 FROM t_sales T
 LEFT JOIN m_customer M
 ON T.code = M.code
t_salesのcodeに一致するm_customerが無い場合、M.nameはNULLになります。
結合先のテーブルが存在しているかどうかの判断には、
NULLが入らないカラムを使ってNULL判定(IS NULL または IFNULL)することで行えます。

複数のJOINを組み合わせた使用例

以下のクラスを使用して、JOINを使ったSQLを発行するVBAサンプルです。
VBAクラスの全コード:データの取得
SQL入門の「データの取得:条件指定(SELECT,WHERE)」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール:clsSQLite
Sub SelectJoin()
  Dim clsDB As New clsSQLite
  clsDB.DataBase = "C:\SQLite3\sample.db"
  
  Dim ws As Worksheet
  Set ws = ActiveSheet
  
  Dim sSql As String
  sSql = ""
  sSql = sSql & "SELECT" & vbCrLf
  sSql = sSql & " T.code,M1.name,M1.address,T.sales_date" & vbCrLf
  sSql = sSql & ",T.item_code,IFNULL(M2.item_name,'マスタなし')" & vbCrLf
  sSql = sSql & ",T.item_price,T.item_count" & vbCrLf
  sSql = sSql & ",T.item_price * T.item_count AS item_amount" & vbCrLf
  sSql = sSql & ",T.comment" & vbCrLf
  sSql = sSql & " FROM t_sales T" & vbCrLf
  sSql = sSql & " INNER JOIN m_customer M1" & vbCrLf
  sSql = sSql & " ON T.code = M1.code" & vbCrLf
  sSql = sSql & " LEFT JOIN m_item M2" & vbCrLf
  sSql = sSql & " ON T.item_code = M2.item_code" & vbCrLf
  sSql = sSql & " WHERE T.code = '001'"
  
  If Not clsDB.SheetFromRecordset(sSql, ws.Range("A1"), Clear, True) Then
    MsgBox clsDB.ErrMsg
    Exit Sub
  End If
  
  Set clsDB = Nothing
End Sub
※イミディエイ等でsSqlを見た時に見やすいようにvbCrLfを入れています。

t_salesとm_customerを内部結合(一致するものだけ)し、そこにm_itemを外部結合しています。
外部結合しているm_itemのitem_nameはデータが無い時には'マスタなし'となります。

通常、トランザクションデータにマスタ情報を付加する場合はLEFT JOINを使います。
ここでは、SQLサンプルとして、あえてINNER JOINを使用しています。

VBA マクロ SQL JOIN

INNER JOINの場合、SELECTの出力カラムにおいては、
両方のテーブルに存在するカラム(ここではcode)は、どちらのテーブルを使用しても同じです。
したがって上記SQLのSELECTでは、T.codeの代わりにM1.codeとしても結果は同じになります。

※前回作成したマスタ
前回作静したマスタは、全てのデータの整合性を取っているので、
INNERでもLEFTでも結果は同じになってしまいます。
それぞれのテーブルにデータを追加して、JOINの違いを確認しておきましょう。

JOINを使わない内部結合

JOINを使わずに複数のテーブルを結合するSQLの書き方があります。

SELECT T.code,M.name
 FROM t_sales T
 INNER JOIN m_customer M
 ON T.code = M.code
これは以下のようにJOINを使わずにSQLを書くことができます。

SELECT T.code,M.name
 FROM t_sales T
     ,m_customer M
 WHERE T.code = M.code
この書き方では外部結合を書くことはできません。
(ただしOracleには(+)で外部結合する書き方があります。)

このSQLの書き方は古い書き方とされていて、現在は使うべきではありません。
ただし、既に書かれているSQLで使われている場合もあるので、読めるようにはしておきましょう。

テーブルを結合して取得の最後に

テーブルの結合は、SQLの重要かつ基本になります。
上記でも説明していますが、実際に使えるようになっておくべき必須の結合方法は、
INNER JOIN
LEFT JOIN
この2つだけになりますので、これはしっかり習得しておきましょう。

JOINは横にデータを結合するものでしたが、SQLではデータを縦に連結することもできます。
次回はこの縦に連結する、複数のSELECTした結果を統合するUNIONについて解説します。



同じテーマ「SQL入門」の記事

データベースにおけるNULLの扱い方
データベースの正規化とマスタの作成
全テーブル定義とテーブル自動作成VBA
テーブルを結合して取得(INNER JOIN,OUTER JOIN)
複数のSELECT結果を統合(UNION,UNION ALL)
データの更新(UPDATE)
データの削除(DELETE)
他のテーブルのデータで追加/更新/削除
インデックスを作成して高速化(CREATE INDEX)
トランザクション処理
VBAクラスの全コード:トランザクション処理


新着記事NEW ・・・新着記事一覧を見る

ツイッターで出されたVBAのお題をやってみた|エクセル雑感(1月13日)
イベントプロシージャーの共通化(Enter,Exit)|ユーザーフォーム入門(1月13日)
Rangeオブジェクトの論理演算(差集合と排他的論理和)|VBA技術解説(1月10日)
イベントプロシージャーの共通化|ユーザーフォーム入門(1月7日)
コントロールの動的作成|ユーザーフォーム入門(1月6日)
Evaluateメソッド(文字列の数式を実行します)|VBA技術解説(1月5日)
エクスポート(PDF/XPS)|VBA入門(1月2日)
分析関数(OVER句,WINDOW句)|SQL入門(12月25日)
取得行数を限定するLIMIT句|SQL入門(12月21日)
外部ライブラリ(ActiveXオブジェクト)|VBA入門(12月21日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ