12. 結合

オンライン教材 > MySQL超入門

JOINとは

データの結合

JOINは、複数のテーブル間で関連するデータを結合するためのSQLです。JOINを使用すると複数のテーブルの列を関連づけながら、包括的な結果を取得することができます。

リレーションの確認

JOINを利用するには、テーブルのリレーションを確認する必要があります。各テーブルのカラムの値がリレーション（関連）していることが前提です。

ER図

リレーションを視覚的に理解するのに、ER図というデータベーステーブルの関連図があります。phpMyAdmin*ではメニューの「デザイナ」からER図が表示できます。

ER図は、DDLであらかじめリレーションの定義していれば、テーブルカラムが線で結ばれます。

ER図

JOINの種類

RDBでは、いくつかのJOINの種類が利用できます。JOINは、内部結合（INNER） と外部結合（OUTER） にわかれます。

INNER JOIN

INNER JOIN（JOIN）は2つのテーブルで共有される行を内部結合し、共有値が一致する行のみが結果に表示されます。

LEFT JOIN

LEFT JOIN（LEFT OUTER JOIN）は、左側のテーブルのすべての行を保持し、右側のテーブルの共有される行を外部結合します。右側のテーブルに一致する行がない場合、NULL値が表示されます。

RIGHT JOIN

RIGHT JOIN（RIGHT OUTER JOIN）ともいい、右側のテーブルのすべての行を保持し、左側のテーブルの共有される行を外部結合します。左側のテーブルに一致する行がない場合、NULL値が表示されます。

FULL JOIN

FULL JOIN（FULL OUTER JOIN）ともいい、左右のテーブルのすべての行を保持し、共有される行を完全外部結合します。一致しない場合は、NULL値が表示されます。

  • MySQLでは FULL JOIN は利用できないため、LEFTとRIGHTを組み合わせます。

基本構文

SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;

INNER JOIN

INNER JOINの基本

「user_items.user_id」と「users.id」がリレーションキーで、INNER JOIN（内部結合）します。

SELECT 
  users.id,
  users.name,
  user_items.user_id, 
  user_items.item_id, 
  user_items.created_at
FROM user_items 
INNER JOIN users ON user_items.user_id = users.id;

「users.id = user_items.user_id」のレコードのみ選択されました。

結果例
id name user_id item_id created_at	
1 三宅 直子 1 1 2023-06-12 13:39:45
2 渡辺 直人 2 1 2023-06-12 13:39:47
1 三宅 直子 1 3 2023-06-12 13:40:45
3 三宅 聡太郎 3 2 2023-06-12 13:41:02

INNERの省略

INNER JOINは、JOINと省略できます

SELECT 
  users.id,
  users.name,
  user_items.user_id, 
  user_items.item_id, 
  user_items.created_at
FROM user_items 
JOIN users ON user_items.user_id = users.id;

順番を変えても基本は同じ

INNER JOIN は、FROMとJOINの順番を変えても、基本的に結果は同じになります。

SELECT 
  users.id,
  users.name,
  user_items.user_id, 
  user_items.amount, 
  user_items.created_at
FROM users 
JOIN user_items ON user_items.user_id = users.id;
結果例
id name user_id amount created_at	
1 三宅 直子 1 3 2023-06-12 13:39:45
2 渡辺 直人 2 1 2023-06-12 13:39:47
1 三宅 直子 1 2 2023-06-12 13:40:45
3 三宅 聡太郎 3 2 2023-06-12 13:41:02

「user_items」に「items」を INNER JOIN

「user_items.item_id」と「items.id」がリレーションキーで、INNER JOINします。

SELECT 
  items.id,
  items.name,
  user_items.user_id, 
  user_items.item_id, 
  user_items.created_at
FROM user_items 
INNER JOIN items ON user_items.item_id = items.id;

「items.id = user_items.item_id」のレコードのみ選択されました。

結果例
id	name user_id item_id	created_at	
1 コーヒー 1 1 2023-06-12 13:39:45
1 コーヒー 2 1 2023-06-12 13:39:47
2 紅茶 3 2 2023-06-12 13:41:02
3 ほうじ茶 1 3 2023-06-12 13:40:45

OUTER JOIN

LEFT JOIN

「users」に「user_items」を LEFT JOIN

「users」を基準に「users.id = user_items.user_id」でLEFT JOINします。

SQL
SELECT 
  users.id,
  users.name,
  user_items.user_id, 
  user_items.item_id, 
  user_items.created_at
FROM users 
LEFT JOIN user_items ON user_items.user_id = users.id;

「users」（左）に「user_items」（右）がLEFTで結合し、関連するレコードが表示されました。一致しないレコードはNULLとなります。

id name user_id amount created_at	
1 三宅 直子 1 3 2023-06-12 13:39:45
2 渡辺 直人 2 1 2023-06-12 13:39:47
1 三宅 直子 1 2 2023-06-12 13:40:45
3 三宅 聡太郎 3 2 2023-06-12 13:41:02
4 原田 真綾 NULL NULL NULL
5 井上 亮介 NULL NULL NULL
...

「user_items」に「users」を LEFT JOIN

今度は「user_items」テーブルを基準に「users.id = user_items.user_id」でLEFT JOINします。

SQL
SELECT 
  users.id,
  users.name,
  user_items.user_id, 
  user_items.item_id, 
  user_items.created_at
FROM user_items 
LEFT JOIN users ON user_items.user_id = users.id;

「user_items」（左）に「users」（右）がLEFTで結合し、関連するレコードが表示されました。

結果例
id name user_id amount created_at	
1 三宅 直子 1 3 2023-06-12 13:39:45
2 渡辺 直人 2 1 2023-06-12 13:39:47
1 三宅 直子 1 2 2023-06-12 13:40:45
3 三宅 聡太郎 3 2 2023-06-12 13:41:02
結果

RIGHT JOIN

「user_items」を基準

「user_items」でRIGHT JOINします。

SQL
SELECT 
  users.id,
  users.name,
  user_items.user_id, 
  user_items.item_id, 
  user_items.created_at
FROM users RIGHT JOIN user_items 
ON user_items.user_id = users.id;

結果的に、INNER JOINと同じになりました。

結果例

右側を「users」

「users」でRIGHT JOINします。

SQL
SELECT 
  users.id,
  users.name,
  user_items.user_id, 
  user_items.item_id, 
  user_items.created_at
FROM user_items RIGHT JOIN users 
ON user_items.user_id = users.id;

結果的に「users」を左側とした LEFT JOINと同じになりました。

結果

基本は INNER JOIN と LEFT JOIN

INNER JOINとLEFT JOINだけで、通常の結合処理ができます。RIGHT JOINは利用頻度が少ないかもしれません。

複数のJOIN

複数のJOINを使用することで、複数のテーブルと結合できるため、より複雑なデータ操作ができます。

3つのテーブルのINNER JOIN

table1、table2、table3の3つのテーブルをINNER JOINで結合しています。

SELECT *
FROM table1
INNER JOIN table2 ON table1.key = table2.key
INNER JOIN table3 ON table2.key = table3.key;

JOINの順序に注意

JOINによる結合の順序によって、結果が異なることがあるため注意が必要です。適切な結合順序を指定しましょう。

「user_items」に「users」と「items」を内部結合

SELECT 
  users.name,
  items.name,
  user_items.amount,
  user_items.total_price,
  user_items.created_at
FROM user_items 
JOIN users ON user_items.user_id = users.id
JOIN items ON user_items.item_id = items.id;
<< 関数

MySQL超入門