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

Oracle『外部結合の使い方』複数のテーブルを接続してデータを取得するOracle SQL入門
2023.05.16
  1. テーブル結合とは？
  2. 外部結合とは？
  3. 外部結合の書き方
  4. 外部結合の種類と使い方
    1. 左側のテーブルを主にして結合する（LEFT JOIN）
    2. 右側のテーブルを主にして結合する（RIGHT JOIN）
    3. 両方のテーブルを主にして結合する（FULL JOIN）
  5. 『(+)』を使った結合について
  6. 外部結合の使い方　まとめ

テーブル結合とは？

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

その１つ１つには、同じ意味を持ったデータがそれぞれ入っていますが、
それらはテーブルの中だけで完結するのではなく、

お互いに情報を組合せることでさらに意味を持つこともあります。

　握手

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

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

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

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

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

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

外部結合とは？

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

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

また、共通しない場合でも結合先のデータがないものとして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を使ってテーブルからデータを取得する方法を覚えましょう。 スキーマ、テーブルの指定やエイリアスについてもあわせて解説します。
it-road.com
2023.06.04

外部結合の種類と使い方

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

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

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

論理名物理名データ型主キー
クラスCLASS_NOCHAR(1)1
出席番号STUDENT_NONUMBER(3)2
名前NAMEVARCHAR2(30)
【表１】STUDENTテーブルの定義
クラス出席番号名前
11ジョニー
12ボビー
13カレン
21デキスギ
22ノビタ
【表２】STUDENTテーブルのデータ

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

論理名物理名データ型主キー
クラスCLASS_NOCHAR(1)1
出席番号STUDENT_NONUMBER(3)2
部活CLUBVARCHAR2(30)
【表3】CLUBテーブルの定義
クラス出席番号部活
11サッカー
13テニス
21サッカー
22射撃
23野球
【表4】CLUBテーブルのデータ

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

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

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

Oracle_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

実行結果は以下です。

Oracle_LEFT_JOINの実行結果

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

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

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

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

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

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

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

Oracle_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

実行結果は以下です。

Oracle_RIGHT_JOINの実行結果

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

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

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

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

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

Oracle_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

実行結果は以下です。

Oracle_FULL_JOINの実行結果

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

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」を使うようにしましょう！

外部結合の使い方　まとめ

Oracleの外部結合について以下説明しました。

・テーブル結合について

・外部結合について

・外部結合の書き方

・外部結合の種類と使い方（LEFT、RIGHT、FULL）

・旧バージョンの記載方法について

外部結合はSQLを使う上で必須の構文です。

また、データベースから効率よくデータを取得する為には、
テーブル同士の関係性を理解して、
結合キーを正しく指定するスキルも必要になってきます。

SQL上達への近道はとにかく書くことです。

いろいろなパターンを試して外部結合の動きを理解しておきましょう！