Oracle『外部結合の使い方』複数のテーブルを接続してデータを取得する

【本サイトではGoogleアドセンス、または、アフィリエイト広告を利用しています。】

テーブル結合とは？
外部結合とは？
外部結合の書き方
外部結合の種類と使い方
『(+)』を使った結合について
外部結合の使い方　まとめ

テーブル結合とは？

データベースには複数のテーブルが定義されています。

その１つ１つには、同じ意味を持ったデータがそれぞれ入っていますが、
それらはテーブルの中だけで完結するのではなく、お互いに情報を組合せることでさらに意味を持つこともあります。

例えば、学校の中には、クラスがあって、クラスには生徒がいます。
さらに、生徒はテストを受けたり、部活動に所属することもあります。

生徒の所属クラスと所属する部活動を知りたい時に２つのテーブルを突き合わせて確認するのは大変ですよね？

できれば、複数のテーブルに散らばった情報を１つのデータとしてまとめて取得できると大変便利です。

それを実現するのが今回お話するテーブル結合です！

テーブル結合を使えば、テーブル同士を繋げて１つのまとまりとすることで、
新たな情報をデータベースから得ることができます。

今回はその中でも、外部結合と呼ばれる結合方法についてお話をします。

外部結合とは？

外部結合とは、データベースにある複数のテーブルを１つにまとめる方法の１つです。

外部結合する２つのテーブルは、お互いのデータの共通点を比較して結合します。

また、共通しない場合でも結合先のデータがないものとしてNULLを紐づけします。

つまり、結合先との共通点がない場合でもデータを返すので、
結合元のデータはすべてSELECTされるということになります。

もう少しわかりやすく言えば、１つのテーブルの後ろに、
もう１つのテーブルにあるデータを補足情報として引っ付けるイメージです。

外部結合の書き方

外部結合をする時は以下のようにSQLを書きます。

SELECT
    [テーブル名1 or 2].[カラム名]
    , ・・・
FROM
    [テーブル名1]
    [LEFT / RIGHT / FULL] (OUTER) JOIN [テーブル名2]
        ON [結合キー(テーブル1の任意のカラム1)] = [結合キー(テーブル2の任意のカラム1)]
            AND [結合キー(テーブル1の任意のカラム2)] = [結合キー(テーブル2の任意のカラム2)]
            AND ・・・
WHERE
    ・・・
ORDER BY
    ・・・

外部結合には、LEFT（左側）、RIGHT（右側）、FULL（双方向）の３種類があって、
JOINの前にいずれかを指定します。

また、Oracleでは「OUTER」を省略して書くことができます。

例えば「LEFT OUTER JOIN」については、「LEFT JOIN」でも同じ意味となり、
Oracleとしてはどちらも問題なく動きますが、

「LEFT JOIN」の方がプログラムが読みやすく書きやすいので、
開発者の間では「OUTER」を省略して書くのが一般的になっています。

SQLを書く時にテーブル名が長い場合はエイリアスを使うこともできるので、
読みやすいSQLを書くことを心掛けましょう。

エイリアスの使い方については以下を参考にしてください！

Oracle『SELECT文の使い方』テーブルからデータを取得する

OracleのSELECT文の使い方について説明します。SQLを使ってテーブルからデータを取得する方法を覚えましょう。スキーマ、テーブルの指定やエイリアスについてもあわせて解説します。

外部結合の種類と使い方

外部結合の種類と使い方について、それぞれ以下のサンプルを使いながら説明します。

生徒（STUDENT）テーブルと、部活動（CLUB）テーブルがあり、以下のようなデータが入っているとします。

まずはSTUDENTテーブルです。
2組の出席番号3のデータはあえて用意しません。

論理名	物理名	データ型	主キー
クラス	CLASS_NO	CHAR(1)	1
出席番号	STUDENT_NO	NUMBER(3)	2
名前	NAME	VARCHAR2(30)

【表１】STUDENTテーブルの定義

クラス	出席番号	名前
1	1	ジョニー
1	2	ボビー
1	3	カレン
2	1	デキスギ
2	2	ノビタ

【表２】STUDENTテーブルのデータ

次に、CLUBテーブルです。
1組の出席番号2のデータはあえて用意しません。

論理名	物理名	データ型	主キー
クラス	CLASS_NO	CHAR(1)	1
出席番号	STUDENT_NO	NUMBER(3)	2
部活	CLUB	VARCHAR2(30)

【表3】CLUBテーブルの定義

クラス	出席番号	部活
1	1	サッカー
1	3	テニス
2	1	サッカー
2	2	射撃
2	3	野球

【表4】CLUBテーブルのデータ

左側のテーブルを主にして結合する（LEFT JOIN）

LEFT JOINは、左側にあるテーブルを主として、右側にテーブルを結合する構文です。

データの取得範囲を図で表すと以下のようなイメージです。

例えば、生徒が所属する部活動のデータを取得する時、生徒テーブルを主とした場合、以下のようなSQLになります。

SELECT
    ST.CLASS_NO
    , ST.STUDENT_NO
    , ST.NAME
    , CL.CLUB 
FROM
    STUDENT ST 
    LEFT JOIN CLUB CL 
        ON ST.CLASS_NO = CL.CLASS_NO 
        AND ST.STUDENT_NO = CL.STUDENT_NO 
ORDER BY
    ST.CLASS_NO
    , ST.STUDENT_NO

実行結果は以下です。

1組のボビーに対する部活動データは存在しないので、CLUB列がNULLになっています。

このように、外部結合では結合先となるテーブルにデータがなくとも、
主となるテーブルのデータを取得するという特徴があります。

また、その時、結合先テーブルの対象列はすべてNULLになります。

そして、部活動テーブルにしかない、2組の出席番号3のデータについては、
主となる生徒テーブルにデータがないので取得対象外となる点にも注目しましょう。

右側のテーブルを主にして結合する（RIGHT JOIN）

RIGHT JOINは、右側にあるテーブルを主として、左側にテーブルを結合する構文です。

データの取得範囲を図で表すと以下のようなイメージです。

例えば、生徒が所属する部活動のデータを取得する時、部活動テーブルを主とした場合、以下のようなSQLになります。

SELECT
    ST.CLASS_NO
    , ST.STUDENT_NO
    , ST.NAME
    , CL.CLUB 
FROM
    STUDENT ST 
    RIGHT JOIN CLUB CL 
        ON ST.CLASS_NO = CL.CLASS_NO 
        AND ST.STUDENT_NO = CL.STUDENT_NO 
ORDER BY
    ST.CLASS_NO
    , ST.STUDENT_NO

実行結果は以下です。

2組の出席番号3に対する生徒データは存在しないので、
STUDENTテーブルから取得している列がすべてNULLになっています。

また、2組のボビーに対する部活動データはそもそも存在しないので、
付属情報として扱う生徒データは取得対象とはなりません。

両方のテーブルを主にして結合する（FULL JOIN）

FULL JOINは、左右両方のテーブルを主として、双方向にテーブルを結合する構文です。

データの取得範囲を図で表すと以下のようなイメージです。

例えば、生徒が所属する部活動のデータを取得する時、
生徒テーブルと部活動テーブルの両方を主とした場合、以下のようなSQLになります。

SELECT
    ST.CLASS_NO
    , ST.STUDENT_NO
    , ST.NAME
    , CL.CLUB 
FROM
    STUDENT ST 
    FULL JOIN CLUB CL 
        ON ST.CLASS_NO = CL.CLASS_NO 
        AND ST.STUDENT_NO = CL.STUDENT_NO 
ORDER BY
    ST.CLASS_NO
    , ST.STUDENT_NO

実行結果は以下です。

結合先のデータ有無にかかわらず、
生徒テーブルと部活動テーブルの両方のデータがすべて取得対象となっていることがわかります。

FULL JOINは、このような特徴を利用して、データの登録状況などをチェックする際に使うこともできます。

『(+)』を使った結合について

Oracleの外部結合の書き方は先ほどお話した通りですが、旧バージョンの書き方が存在します。

旧バージョンのOracleを利用してシステムを開発していた場合、以下のようなSQLが組み込まれている可能性もある為、
実行時にエラーとならないよう、新バージョンでも旧バージョンの文法で外部結合できるようになっています。

例えば、先ほどのLEFT JOINのサンプルを旧バージョン風に書き換えると以下のようになります。

SELECT
    ST.CLASS_NO
    , ST.STUDENT_NO
    , ST.NAME
    , CL.CLUB 
FROM
    STUDENT ST
    , CLUB CL 
WHERE
    ST.CLASS_NO = CL.CLASS_NO(+) 
    AND ST.STUDENT_NO = CL.STUDENT_NO(+) 
ORDER BY
    ST.CLASS_NO
    , ST.STUDENT_NO

旧バージョンでは、ON句ではなく、WHERE句の中で結合先のキーに「(+)」をつけて条件を指定します。

例えば、LEFT JOINする場合、右側の結合キーに「(+)」をつけます。

この書き方は、システムの改修作業をしていると見かけることがありますが、
データの絞り込み用途で記載している条件と、外部結合として指定している条件の区別がつきにくく、
メンテナンス性も可読性も新バージョンに比べて悪いです。

以上の理由から、Oracleとしても公式に非推奨として提示されているので、
新しくSQLを書く時は、新文法の「JOIN」を使うようにしましょう！