【Oracle】誰でもわかるテーブル結合！

今回は、複数のテーブルからデータを一度に取得する方法について説明します。複数のテーブルを結合するとことによって、一度でデータを取得できるのですが取得する方法は２つあります。「 INNER JOIN（内部結合）」と「OUTER JOIN(外部結合)」 についてこの記事を読み終わる頃には理解できているはずです！

データ準備

INNER JOIN（内部結合）とOUTER JOIN(外部結合)を試すために、テーブルを作成しデータを追加します。

-- 生徒テーブルの作成
CREATE TABLE students (
    person_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    nationality VARCHAR2(50)
);

-- 海外渡航歴テーブルの作成
CREATE TABLE travel_histories (
    travel_id NUMBER PRIMARY KEY,
    person_id NUMBER,
    destination VARCHAR2(100),
    travel_date DATE
);

-- データ追加（生徒テーブル）
INSERT
  ALL INTO students
VALUES
  (1, 'John', 'Smith', 'USA')
INTO
  students
VALUES
  (2, 'Emma', 'Johnson', 'Canada')
INTO
  students
VALUES
  (3, 'Pierre', 'Dupont', 'France')
INTO
  students
VALUES
  (4, 'Maria', 'Garcia', 'Spain')
INTO
  students
VALUES
  (5, 'Hans', 'Mueller', 'Germany')
INTO
  students
VALUES
  (6, 'Chiaki', 'Sato', 'Japan')
INTO
  students
VALUES
  (7, 'Luis', 'Rodriguez', 'Mexico')
INTO
  students
VALUES
  (8, 'Elena', 'Ivanova', 'Russia')
INTO
  students
VALUES
  (9, 'Chen', 'Wei', 'China')
INTO
  students
VALUES
  (10, 'Santiago', 'Lopez', 'Argentina')
SELECT
  *
FROM
  DUAL;

-- データ追加（海外渡航歴テーブル） 
INSERT
  ALL INTO travel_histories
VALUES
  (1, 3, 'USA', TO_DATE('2023-01-01', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (2, 2, 'Canada', TO_DATE('2022-12-15', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (3, 5, 'France', TO_DATE('2022-11-20', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (4, 7, 'Spain', TO_DATE('2022-10-30', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (5, 8, 'Germany', TO_DATE('2022-09-10', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (6, 6, 'China', TO_DATE('2022-08-25', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (7, 9, 'Mexico', TO_DATE('2022-07-05', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (8, 1, 'Italy', TO_DATE('2022-06-20', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (9, 3, 'Brazil', TO_DATE('2022-05-10', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (10, 2, 'Australia', TO_DATE('2022-04-15', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (11, 9, 'UK', TO_DATE('2022-03-25', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (12, 7, 'Russia', TO_DATE('2022-02-05', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (13, 2, 'India', TO_DATE('2022-01-15', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (14, 1, 'South Africa', TO_DATE('2021-12-20', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (15, 2, 'Japan', TO_DATE('2021-11-30', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (16, 3, 'Argentina', TO_DATE('2021-10-10', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (17, 3, 'Switzerland', TO_DATE('2021-09-05', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (18, 8, 'Netherlands', TO_DATE('2021-08-15', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (19, 9, 'Sweden', TO_DATE('2021-07-25', 'YYYY-MM-DD'))
INTO
  travel_histories
VALUES
  (20, 1, 'New Zealand', TO_DATE('2021-06-05', 'YYYY-MM-DD'))
SELECT
  *
FROM
  DUAL;

今回追加したテーブルは「生徒テーブル」と「海外渡航歴テーブル」でどの生徒がどんな国に行ったことがあるのか調査してみたいと思います！

イメージとしては、生徒番号（person_id）をキーに２つのテーブルをくっつけてデータを取得するのですが、例えば生徒番号が「6番」のChiakiさんはどんな国に行ったことがあるのか「INNER JOIN」を使って取得してみました。

では、INNER JOINとOUTER JOINについて、使い方とどんな時に使うのか詳しく確認していきましょう。

INNER JOIN（内部結合）

INNER JOINは、条件にマッチしたデータだけ取得したい場合に使用します。INNER JOINの書き方は下記になります。

SELECT 列 名
FROM テ ー ブ ル 1
  INNER JOIN テ ー ブ ル 2 ON 条件 (テ ー ブ ル 1.キ ー と な る 列 名 = テ ー ブ ル 2.キ ー と な る 列 名)

上記のように生徒番号「2番」のEmmaさんが行った国を調べるにはどうしたらいいのか？画像では、生徒番号〜行き先までを取得していますが、生徒番号と行き先を取得し、さらに海外渡航歴テーブルには20件のデータがあるので検索条件は生徒番号(person_id)で絞ってみましょう！

SELECT
  s.person_id AS "生徒番号",
  th.destination AS "行き先"
FROM
  students s
  INNER JOIN travel_histories th ON s.person_id = th.person_id
WHERE
  s.person_id = 2

テーブル名の後ろに別名 s や th をつけていますが、これはテーブル名を省略しコードをみやすくしています。また、複数を結合する際にカラム名が被ってしまうことがあります。カラム名が被ってしまうとどちら側のテーブルのカラムなのか判断できずにエラー(※)が起こってしまいます。なので、JOINする時は必ずどちらのカラム名なのか特定できるようにテーブルの後ろに別名をつけておきましょう！

※ORA-00918: column ambiguously defined

OUTER JOIN(外部結合)

OUTER JOIN は条件にマッチしないデータも取得したい場合に使用します。書き方は INNER JOIN と同じで INNER JOIN と書いていた箇所を「LEFT OUTER JOIN 」 or 「RIGHT OUTER JOIN」 or 「FULL OUTER JOIN」にするだけです。

SELECT 列 名
FROM テ ー ブ ル 1
-- LEFT OUTER JOIN が使いたい場合
  LEFT OUTER JOIN テ ー ブ ル 2 ON 条件 (テ ー ブ ル 1.キ ー と な る 列 名 = テ ー ブ ル 2.キ ー と な る 列 名)

LEFT OUTER JOIN 、RIGHT OUTER JOIN、FULL OUTER JOIN はどのテーブルをメインにするかで決めます。左側のテーブルのデータを全て含めたデータを取得する場合は LEFT OUTER JOIN を使い、右側のテーブルのデータを全て含めたデータを取得する場合には RIGHT OUTER JOIN を使います。どちらのテーブルのデータも全て欲しいんだって時は、FULL OUTER JOIN を使います。

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

実は、今回追加したデータで海外旅行をしたことがない生徒が2名います。INNER JOIN ではそのデータまで確認することができないので LEFT OUTER JOIN を使って確認してみます。

※OUTER JOINでデータがないフィールドはNULLになります

SELECT
  s.person_id AS "生徒番号",
  s.last_name AS "性",
  s.first_name AS "名",
  s.nationality AS "国籍",
  th.destination AS "行き先"
FROM
  students s
  LEFT OUTER JOIN travel_histories th ON s.person_id = th.person_id
WHERE
  th.destination IS NULL

まとめ

INNER JOINは、条件にマッチしたデータだけ取得したい場合に使用して、OUTER JOIN は条件にマッチしないデータも取得したい場合に使用する。また、テーブルに別名をつけてカラム名の被りを回避する！といったところを覚えておきましょう。