【MySQL】テーブルを外部結合する(left outer join や right outer join の使い方)

テーブルを外部結合するには「OUTER JOIN」を使います。

ここでは MySQLコマンド「LEFT OUTER JOIN(LEFT JOIN)」や「RIGHT OUTER JOIN(RIGHT JOIN)」の具体的な使い方を解説していきます。

メモ

テーブルの結合方法の種類について詳しくはこちら

 

外部結合とは?

外部結合は、指定したカラムの値が一致するデータを結合し、それに加えて元となるテーブルにしか存在しないデータも取得する方法です。

元となるテーブルは LEFT もしくは RIGHT で指定することができます。

 

LEFT を「users」テーブル、RIGHT を「items」テーブルとした場合に、それぞれの取得結果例を以下から見ていきましょう。

 

LEFT OUTER JOIN(LEFT JOIN)

以下は商品IDを使って LEFT OUTER JOIN(LEFT JOIN)で外部結合する例。

「users」テーブルと「items」テーブルの商品IDが一致しているデータが結合され、それに加えて元となる「users」テーブルにしか存在しないデータも取得されます。

 

このとき「users」テーブルにあるデータは結果にすべて表示されます。ただし、商品IDが 5 (ユーザー名: 田中)のデータは、「items」テーブルに対応するデータが存在しないので、商品名と価格のフィールドには何も表示されていません。

また「items」テーブルにある商品IDが 14 のデータは、「users」テーブルの商品IDカラムの値に存在しないので、結果には表示されません。

 

RIGHT OUTER JOIN(RIGHT JOIN)

以下は商品IDを使って RIGHT OUTER JOIN(RIGHT JOIN)で外部結合する例。

「users」テーブルと「items」テーブルの商品IDが一致しているデータが結合され、それに加えて元となる「items」テーブルにしか存在しないデータも取得されます。

 

このとき「items」テーブルにあるデータは結果にすべて表示されます。ただし、商品IDが 1 (商品名: パン)と 4 (商品名: )のデータは、「users」テーブルに対応するデータが存在しないので、ユーザー名のフィールドには何も表示されていません。

また「users」テーブルにある商品IDが 5 のデータは、「items」テーブルの商品IDカラムの値に存在しないので、結果には表示されません。

 

「OUTER JOIN」の基本

「OUTER JOIN」は、「SELECT」などと組み合わせて使います。

以下のコマンドで、テーブル1とテーブル2を外部結合することができます。

ON のあとには、テーブル同士を結合するためのカラム名を指定します。

また、LEFT OUTER JOIN は LEFT JOINRIGHT OUTER JOIN は RIGHT JOIN と省略することがよくあります。

LEFT JOIN

SELECT * FROM テーブル1の名前 LEFT JOIN テーブル2の名前 ON テーブル1のカラム名 = テーブル2のカラム名;

RIGHT JOIN

SELECT * FROM テーブル1の名前 RIGHT JOIN テーブル2の名前 ON テーブル1のカラム名 = テーブル2のカラム名;
メモ

SELECT 文の詳しい使い方はこちら

 

LEFT JOIN を使って テーブルを外部結合する

実際にMySQLサーバーに接続し、LEFT JOIN を使って テーブルを外部結合してみましょう。

 

あらかじめ「shopping」データベースの「users」テーブルには、購入ユーザー名と買った商品ID、「items」テーブルには商品IDと その商品名や価格が保存されている例で進めていきます。

具体的なデータは以下。

「users」テーブル

name item_id
山田 2
鈴木 3
佐藤 2
田中 5

「items」テーブル

id name price
1 パン 100
2 牛乳 200
3 チーズ 150
4 100

データを保存するコマンド例

CREATE DATABASE shopping;
USE shopping;

CREATE TABLE users (name VARCHAR(100), item_id INT);
INSERT INTO users (name, item_id) VALUES ('山田', 2);
INSERT INTO users (name, item_id) VALUES ('鈴木', 3);
INSERT INTO users (name, item_id) VALUES ('佐藤', 2);
INSERT INTO users (name, item_id) VALUES ('田中', 5);

CREATE TABLE items (id INT, name VARCHAR(100), price INT);
INSERT INTO items (id, name, price) VALUES (1, 'パン', 100);
INSERT INTO items (id, name, price) VALUES (2, '牛乳', 200);
INSERT INTO items (id, name, price) VALUES (3, 'チーズ', 150);
INSERT INTO items (id, name, price) VALUES (4, '卵', 100);
メモ

CREATE DATABASE 文の詳しい使い方はこちら

USE 文の詳しい使い方はこちら

CREATE TABLE 文の詳しい使い方はこちら

INSERT INTO 文の詳しい使い方はこちら

 

それでは「users」テーブルと「items」テーブルを結合してみましょう。コマンドは以下。

SELECT * FROM users LEFT JOIN items ON users.item_id = items.id;
メモ

ひとつのコマンド内に複数のテーブルが出てくるとき、カラム名を指定するときは、手前にテーブル名をつけて テーブル名.カラム名 という形で記述します。

 

コマンドを実行すると、以下のように結果が表示されました。

このとき、対応する「items」テーブルのデータが存在しない場合は、NULL となっています。

メモ

NULL について詳しくはこちら

 

「SELECT」のあとに続けて、以下のように表示したいカラム名を指定することもできます。

SELECT users.name, items.name, items.price FROM users LEFT JOIN items ON users.item_id = items.id;

 

コマンドを実行すると、以下のように結果が表示されました。

 

RIGHT JOIN を使って テーブルを外部結合する

今度は RIGHT JOIN を使って テーブルを外部結合してみましょう。コマンドは以下。

SELECT * FROM users RIGHT JOIN items ON users.item_id = items.id;

 

コマンドを実行すると、以下のように結果が表示されました。

このとき、対応する「users」テーブルのデータが存在しない場合は、NULL となっています。

 

「SELECT」のあとに続けて、以下のように表示したいカラム名を指定することもできます。

SELECT users.name, items.name, items.price FROM users RIGHT JOIN items ON users.item_id = items.id;

 

コマンドを実行すると、以下のように結果が表示されました。

 

ここまでが「OUTER JOIN」の基本!

理解をさらに深めたい方は以下からの内容もご覧ください。

 

3つ以上のテーブルを結合する

3つ以上のテーブルを結合したい場合は、以下のようにして さらに後ろに「OUTER JOIN」の文をつなげます。

SELECT * FROM users LEFT JOIN items ON users.item_id = items.id LEFT JOIN items2 ON users.item_id = items2.id;

 

USINGを使った外部結合

「OUTER JOIN」を行う際に、テーブル同士を結合するための両テーブルのカラム名が同じ場合は、ON の代わりに USING を使うこともできます。

「users」テーブルと「items」テーブルの「item_id」カラムを使って外部結合するコマンド例

SELECT * FROM users LEFT JOIN items USING(item_id);

 

まとめ

以上、MySQLコマンド「LEFT OUTER JOIN(LEFT JOIN)」や「RIGHT OUTER JOIN(RIGHT JOIN)」の使い方でした!

 

ここまでの内容をまとめておきます。

  • 外部結合は、指定したカラムの値が一致するデータを結合し、それに加えて元となるテーブルにしか存在しないデータも取得する方法。
  • 元となるテーブルは LEFT もしくは RIGHT で指定することができる。
  • 「LEFT OUTER JOIN(LEFT JOIN)」や「RIGHT OUTER JOIN(RIGHT JOIN)」コマンドでテーブルを外部結合することができる。