テーブルの結合 JOIN

　テーブルの結合とは、複数のテーブルデータを組み合わせる方法です。

　例えば、商品マスターには商品名があって売上データには商品IDのみがある場合に、売上データから商品名を知ることができません。
　そのような場合には商品マスターと売上データを結合することで商品名のある売上データを取得することができます。

　テーブルの結合は、JOIN 句を使用して複数のテーブルを組み合わせます。

　JOIN 句のみを複数のテーブルを結合すると、それぞれのテーブルのデータの全組み合わせで返されます。

SELECT * FROM goods JOIN sales_slip;

　JOIN 句のみではすべての組み合わせを返します。組み合わせに条件を設定して任意の行のみを抽出するには ON を付加します。

SELECT * FROM goods AS g JOIN sales_slip AS s
	ON g.id = s.goods_id;

　テーブルの結合には外部結合と内部結合があります。JOIN 句はデフォルトの設定では内部結合です。
　JOIN 句の代わりに INNER JOIN 句によって明確に内部結合であることを指定することもできます。

SELECT * FROM goods AS g INNER JOIN sales_slip AS s
	ON g.id = s.goods_id;

例のデータについて

　以降の例では、テーブルページの外部キー制約（ FOREIGN KEY ）の作成例で使用したデータを使用しています。

　以下のようなデータです。

SELECT * FROM goods;
+----+----------------+------------+
| id | name           | unit_price |
+----+----------------+------------+
|  1 | candy          |          3 |
|  2 | snack          |          5 |
|  3 | chocolate      |          8 |
|  4 | ice            |          8 |
|  5 | lollipop candy |         15 |
+----+----------------+------------+
...

select * from sales_slip;
+----+---------------------+----------+------------+--------+
| id | date_time           | goods_id | unit_price | amount |
+----+---------------------+----------+------------+--------+
|  1 | 2023-09-26 22:29:57 |        1 |          3 |    100 |
|  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |
|  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |
|  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |
+----+---------------------+----------+------------+--------+
...

JOIN

　JOIN 句は複数のテーブルを結合します。JOIN のみでテーブルを指定するとすべての組み合わせになります。

　以下のデータは上記の例のデータについてを使用しています。

SELECT * FROM goods JOIN sales_slip;

+----+----------------+------------+----+---------------------+----------+------------+--------+
| id | name           | unit_price | id | date_time           | goods_id | unit_price | amount |
+----+----------------+------------+----+---------------------+----------+------------+--------+
|  5 | lollipop candy |         15 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |
|  4 | ice            |          8 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |
|  3 | chocolate      |          8 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |
|  2 | snack          |          5 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |
|  1 | candy          |          3 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |
|  5 | lollipop candy |         15 |  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |
|  4 | ice            |          8 |  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |
|  3 | chocolate      |          8 |  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |
|  2 | snack          |          5 |  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |
|  1 | candy          |          3 |  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |
|  5 | lollipop candy |         15 |  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |
|  4 | ice            |          8 |  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |
|  3 | chocolate      |          8 |  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |
|  2 | snack          |          5 |  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |
|  1 | candy          |          3 |  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |
|  5 | lollipop candy |         15 |  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |
|  4 | ice            |          8 |  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |
|  3 | chocolate      |          8 |  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |
|  2 | snack          |          5 |  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |
|  1 | candy          |          3 |  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |
|  5 | lollipop candy |         15 |  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |
|  4 | ice            |          8 |  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |
|  3 | chocolate      |          8 |  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |
|  2 | snack          |          5 |  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |
|  1 | candy          |          3 |  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |
+----+----------------+------------+----+---------------------+----------+------------+--------+

　すべての組み合わせではなく、組み合わせ条件を設定するには ON 句を使用します。ON 句を使用することで条件にあった組み合わせのみを抽出します。

ON 結合条件

　JOIN に対して ON 句によってテーブルの結合条件を付加することができます。

　全組み合わせに対して ON 句による条件をもつ行のみが抽出されます。

> SELECT * FROM goods AS g JOIN sales_slip AS s
	ON g.id = s.goods_id;

+----+-----------+------------+----+---------------------+----------+------------+--------+
| id | name      | unit_price | id | date_time           | goods_id | unit_price | amount |
+----+-----------+------------+----+---------------------+----------+------------+--------+
|  1 | candy     |          3 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |
|  2 | snack     |          5 |  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |
|  2 | snack     |          5 |  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |
|  1 | candy     |          3 |  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |
|  3 | chocolate |          8 |  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |
+----+-----------+------------+----+---------------------+----------+------------+--------+

　このようなテーブルの結合には外部結合と内部結合があります。

　JOIN 句はデフォルトでは内部結合になります。内部結合を明確に指定するには INNER JOIN を使用します。

非等結合

　ON 句における条件でイコール=で評価される場合は等結合といいます。
　範囲による条件も指定することができます。範囲による結合は非等結合といいます。

　以下の例は非等結合ができることを示しているだけで意味がありません。

SELECT * FROM goods AS g JOIN sales_slip AS s
	ON g.id >= s.goods_id;

ON 句と WHERE 句

　テーブル結合に WHERE 句による抽出を加えるとその条件による行データのみが取得できます。

SELECT * FROM goods AS g JOIN sales_slip AS s
	ON g.id = s.goods_id
	WHERE amount >= 50;

+----+-------+------------+----+---------------------+----------+------------+--------+---------+
| id | name  | unit_price | id | date_time           | goods_id | unit_price | amount | user_id |
+----+-------+------------+----+---------------------+----------+------------+--------+---------+
|  1 | candy |          3 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |       1 |
|  2 | snack |          5 |  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 |
|  1 | candy |          3 |  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |
+----+-------+------------+----+---------------------+----------+------------+--------+---------+

　このことは ON 句で AND を使用しても同じことが起きます。

SELECT * FROM goods AS g JOIN sales_slip AS s
	ON g.id = s.goods_id
	AND amount >= 50;

+----+-------+------------+----+---------------------+----------+------------+--------+---------+
| id | name  | unit_price | id | date_time           | goods_id | unit_price | amount | user_id |
+----+-------+------------+----+---------------------+----------+------------+--------+---------+
|  1 | candy |          3 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |       1 |
|  2 | snack |          5 |  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 |
|  1 | candy |          3 |  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |
+----+-------+------------+----+---------------------+----------+------------+--------+---------+

　ただし、処理の順番に違いがあります。同じ結果が得られるのですがコードの意味が目的とした手段に準じているかが大事です。

INNER JOIN

　内部結合を明確に指定するには INNER JOIN を使用します。INNER JOIN も ON 句によって全組み合わせから条件に合う行のみが抽出されます。

　INNER JOIN は JOIN のデフォルトの状態ですが、多くの場合は内部結合を指定には INNER JOIN を使用します。

SELECT * FROM goods AS g INNER JOIN sales_slip AS s
	ON g.id = s.goods_id;

+----+-----------+------------+----+---------------------+----------+------------+--------+
| id | name      | unit_price | id | date_time           | goods_id | unit_price | amount |
+----+-----------+------------+----+---------------------+----------+------------+--------+
|  1 | candy     |          3 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |
|  2 | snack     |          5 |  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |
|  2 | snack     |          5 |  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |
|  1 | candy     |          3 |  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |
|  3 | chocolate |          8 |  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |
+----+-----------+------------+----+---------------------+----------+------------+--------+

サブクエリとの結合

　結合するテーブルはサブクエリにすることもできます。

SELECT * FROM goods AS g
 INNER JOIN ( SELECT * FROM sales_slip WHERE amount >= 50 )  AS s
	ON g.id = s.goods_id;

+----+-------+------------+----+---------------------+----------+------------+--------+---------+
| id | name  | unit_price | id | date_time           | goods_id | unit_price | amount | user_id |
+----+-------+------------+----+---------------------+----------+------------+--------+---------+
|  1 | candy |          3 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |       1 |
|  2 | snack |          5 |  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 |
|  1 | candy |          3 |  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |
+----+-------+------------+----+---------------------+----------+------------+--------+---------+

　上記の例では SELECT 文のサブクエリで抽出された行データを元に結合されます。そのために amount => 50 である行との結合になっています。

自己結合

　同じテーブルを複数回に結合することができます。

　例えば、ユーザーテーブル user_ を作成したとします。事業におけるユーザーには上司であったり部下であったりという相関関係があることがあります。
　user_ テーブルですべてのユーザーを記録するのですが、ユーザーごとの上司を同じ user_ テーブルの id で記録し自己結合するようにします。

user_ テーブル（ MySQL ）

CREATE TABLE user_test (
 	id SERIAL PRIMARY KEY,
 	name VARCHAR(30),
 	manager_id BIGINT UNSIGNED,
 	FOREIGN KEY(manager_id) REFERENCES user_test( id )
);

　PostgreSQL ではUNSIGNEDが使えませんのでmanager_id BIGINTとします。

user_ テーブル（ PostgreSQL ）

CREATE TABLE user_test (
 	id SERIAL PRIMARY KEY,
 	name VARCHAR(30),
 	manager_id BIGINT,
 	FOREIGN KEY(manager_id) REFERENCES user_test( id )
);

　確認用のデータを入力します。

INSERT INTO user_test( name, manager_id ) VALUES ( 'bull', NULL );
INSERT INTO user_test( name, manager_id ) VALUES ( 'tom', 1 );
INSERT INTO user_test( name, manager_id ) VALUES ( 'jenny', 1 );

　上記の user_test テーブルを自己結合で参照しています。ここでは外部結合LEFT OUTER JOINを使用しています。

SELECT u.*, m_u.name AS manager_name
 	FROM user_test AS u
	 LEFT OUTER JOIN user_test AS m_u ON m_u.id = u.manager_id;

+----+-------+------------+--------------+
 | id | name  | manager_id | manager_name |
 +----+-------+------------+--------------+
 |  1 | bull  |       NULL | NULL         |
 |  2 | tom   |          1 | bull         |
 |  3 | jenny |          1 | bull         |
 +----+-------+------------+--------------+

　外部結合は結合する行データがない場合でも抽出の対象になるものです。LEFT OUTER JOINとRIGHT OUTER JOINがあります。用途によって使い分けます。

３つ以上のテーブルの結合

　３つの以上のテーブルを結合する方法はいくつかあるのですが、ここでは１つのテーブルが２つのテーブルの外部キー制約（　FOREIGN KEY　）を持つ場合を例に上げます。

　例えば、sales_slip テーブルに販売担当者を加えたいとします。
　新たに user_ テーブルを作成し、sales_slip テーブルに外部キー制約を設けます。

user_ テーブル

CREATE TABLE user_ (
	id 	SERIAL PRIMARY KEY,	/* ID */
	name 	VARCHAR(30)	/* ユーザー名 */
);

　データは以下のようなものにします。

INSERT INTO user_( name ) VALUES ( 'bull' );
INSERT INTO user_( name ) VALUES ( 'tom' );
INSERT INTO user_( name ) VALUES ( 'jenny' );

　sales_slip テーブルから user_ テーブルを外部キー制約を設けます。

sales_slip の変更（ MySQL ）

CREATE TABLE sales_slip (
	id 		SERIAL PRIMARY KEY,	/* 行番号 */
	date_time 	TIMESTAMP DEFAULT CURRENT_TIMESTAMP,	/* 日付 */
	user_id BIGINT UNSIGNED, 	/* 販売者id */
	goods_id 	BIGINT UNSIGNED,			/* 商品名id */
	unit_price 	INT,			/* 単価 */
	amount 		INT,			/* 数量 */
	FOREIGN KEY(goods_id) REFERENCES goods( id ),
	FOREIGN KEY(user_id) REFERENCES user_( id )
);

　上記の例は MySQL における例です。PostgreSQL ではuser_id INTとします。

　既存のテーブを変更する場合は ALTER クエリーを使用します。

sales_slip の変更（ MySQL ）

ALTER TABLE sales_slip ADD COLUMN user_id BIGINT UNSIGNED;

ALTER TABLE sales_slip ADD CONSTRAINT FOREIGN KEY(user_id) REFERENCES user_( id );

　PostgreSQL では user_id は BIGINT または INT です。

sales_slip の変更（ PostgreSQL ）

ALTER TABLE sales_slip ADD COLUMN user_id INT;

ALTER TABLE sales_slip ADD CONSTRAINT FOREIGN KEY(user_id) REFERENCES user_( id );

　sales_slip テーブルの user_id を以下のように更新します。

UPDATE sales_slip SET user_id = 1 WHERE id = 1;
UPDATE sales_slip SET user_id = 1 WHERE id = 2;
UPDATE sales_slip SET user_id = 1 WHERE id = 3;
UPDATE sales_slip SET user_id = 2 WHERE id = 4;
UPDATE sales_slip SET user_id = 3 WHERE id = 5;

　 sales_slip テーブルと goods、user_テーブルを JOIN で結合してみます。

SELECT * FROM sales_slip
 	JOIN goods
 	JOIN user_;

+----+----------------+------------+----+---------------------+----------+----------------+--------+----+---------------------+----------+------------+--------+---------+
| id | name           | unit_price | id | date_time           | goods_id | purchase_price | amount | id | date_time           | goods_id | unit_price | amount | user_id |
+----+----------------+------------+----+---------------------+----------+----------------+--------+----+---------------------+----------+------------+--------+---------+
|  1 | candy          |          3 |  5 | 2023-10-09 08:36:50 |        5 |             10 |    100 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |       1 |
|  2 | snack          |          5 |  5 | 2023-10-09 08:36:50 |        5 |             10 |    100 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |       1 |
|  3 | chocolate      |          8 |  5 | 2023-10-09 08:36:50 |        5 |             10 |    100 |  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |       1 |
...

　5 ✕ 5 ✕ 3 の組み合わせで 125 通りが表示されますがここでは割愛します。

　sales_slip　の全行データとその商品名と担当したユーザー名が表示される組み合わせの条件は、salse_slip.goods_id = goods.id、salse_slip.user_id = user_.id ですので、このことを ON 条件にそれぞれ加えます。

SELECT * FROM sales_slip
 	JOIN goods ON sales_slip.goods_id = goods.id
 	JOIN user_ ON sales_slip.user_id = user_.id;

+----+---------------------+----------+------------+--------+---------+----+-----------+------------+----+-------+
| id | date_time           | goods_id | unit_price | amount | user_id | id | name      | unit_price | id | name  |
+----+---------------------+----------+------------+--------+---------+----+-----------+------------+----+-------+
|  1 | 2023-10-05 20:46:53 |        1 |          3 |    100 |       1 |  1 | candy     |          3 |  1 | bull  |
|  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 |  2 | snack     |          5 |  1 | bull  |
|  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |       1 |  2 | snack     |          5 |  1 | bull  |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |  1 | candy     |          3 |  2 | tom   |
|  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |       3 |  3 | chocolate |          8 |  3 | jenny |
+----+---------------------+----------+------------+--------+---------+----+-----------+------------+----+-------+

　売り上げた商品の名称と担当したユーザー名が取得できています。

外部結合

　テーブルの結合には内部結合と外部結合があります。外部結合では結合するテーブルの一方のデータをすべて網羅します。
　JOINのみで内部結合として処理されますが、外部結合の場合はOUTER JOINと明記する必要があります。

SELECT * FROM goods AS g
	LEFT OUTER JOIN sales_slip AS s
	ON g.id = s.goods_id;

　さらにOUTER JOINの前にLEFTまたはRIGHTといった先のテーブルの内容を優先するのか後のテーブルのデータを優先するのかと、ONによって結合条件を設定しないとエラーになります。

　外部結合について詳しくは外部結合 OUTER JOINページを参照してください。

自然結合

　SQL の自然結合とは、NATURAL JOIN を使用した結合条件をデータベース任せにするものです。NATURAL JOIN では ON 句を必要としてません。

> SELECT * FROM goods NATURAL JOIN sales_slip;
+----+------------+-------+---------------------+----------+--------+---------+
| id | unit_price | name  | date_time           | goods_id | amount | user_id |
+----+------------+-------+---------------------+----------+--------+---------+
|  1 |          3 | candy | 2023-10-29 07:17:00 |        1 |    100 |       1 |
+----+------------+-------+---------------------+----------+--------+---------+

　自然結合は、必ずしも想定通りになるとは限りません。また、データベースによっても結果が変わる可能性があります。
　ON 句によって結合条件を設定したほうがよいようです。