【練習問題付き】SQLのテーブル結合操作（JOIN、UNION）について

はじめに

データベースを操作し必要な情報を抽出するために、テーブル結合操作を中心に簡単にまとめてみました。

途中で練習問題も作成してみましたので、是非解いてみてください。

結合の種類

SQLにおける結合操作とは、複数のテーブルを組み合わせて一つのテーブルにする操作です。
条件付きの結合操作で主要なのが内部結合と外部結合の2種類です。

• 内部結合・・・条件にマッチしないデータは除外して結合する
• 外部結合・・・条件にマッチしないデータも含めて結合する

外部結合について

外部結合には下記の3種類あります。

• 左外部結合・・・条件が一致しない場合に、左側のテーブルのデータを残す
• 右外部結合・・・条件が一致しない場合に、右側のテーブルのデータを残す
• 完全外部結合・・・条件が一致しない場合に、両方のテーブルのデータを残す

テーブル作成

まず、2つのテーブルを作成し、このテーブルをもとに説明していきます。
小数点以下を扱う場合には、金額のデータ型はprice DECIMAL(10, 2)のようにしてください。

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  product VARCHAR(50),
  price INT
);

それぞれのテーブルにデータを挿入してください。

INSERT INTO users (id, name, age) VALUES
(1, '太郎', 30),
(2, '花子', 25),
(3, '次郎', 35);

INSERT INTO orders (id, user_id, product, price) VALUES
(1, 1, 'りんご', 150),
(2, 2, 'バナナ', 100),
(3, 1, 'オレンジ', 80);

usersテーブル

id	name	age
1	太郎	30
2	花子	25
3	次郎	35

ordersテーブル

id	user_id	product	price
1	1	りんご	150
2	2	バナナ	100
3	1	オレンジ	80

INNER JOIN（内部結合）

内部結合は、2つ以上のテーブルからデータを結合するSQL文です。

INNER JOINの基本的な構文は以下の通りです。

SELECT 列名
FROM テーブル1
INNER JOIN テーブル2
ON テーブル1.キーとなる列名 = テーブル2.キーとなる列名;

この構文は以下の要素から成り立っています。

• SELECT 列名: 結合した結果から取得する列を指定します。複数の列を指定することもでき、記述した順に列が作成されます。
• FROM テーブル1: 指定されたテーブルが基本となり、それに他のテーブルを結合していく形になります。
• INNER JOIN テーブル2: 結合する2番目のテーブルを指定します。
• ON テーブル1.キーとなる列名 = テーブル2.キーとなる列名: 結合条件を指定します。キーとなる列名を指定して2つのテーブルの関連性を確立することで、どの列を基準に行が結合されるかが決まります。

SELECT users.name, orders.product, orders.price
FROM users
INNER JOIN orders
ON users.id = orders.user_id;

name	product	price
太郎	りんご	150
花子	バナナ	100
太郎	オレンジ	80

３つ以上のテーブルでINNER JOIN（内部結合）を使用する場合

3つ以上のテーブルをINNER JOINする場合は、JOIN句を増やしていくことで対応できます。以下のように書きます。

SELECT 列名
FROM テーブル1
INNER JOIN テーブル2
ON テーブル1.キーとなる列名 = テーブル2.キーとなる列名
INNER JOIN テーブル3
ON テーブル2.キーとなる列名 = テーブル3.キーとなる列名;

実際に追加のテーブルを作成して、３つ以上のテーブルでINNER JOINを使用する場合を確認します。

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  category VARCHAR(50)
);

INSERT INTO products (id, name, category) VALUES
(1, 'りんご', 'フルーツ'),
(2, 'バナナ', 'フルーツ'),
(3, 'オレンジ', 'フルーツ');

作成した3つ目のテーブルであるproductsテーブルには、商品の情報が含まれています。
productsテーブル

id	name	category
1	りんご	フルーツ
2	バナナ	フルーツ
3	オレンジ	フルーツ

３つ以上のテーブルでINNER JOINを使用します。

SELECT users.name, orders.product, products.category, orders.price
FROM users
INNER JOIN orders
ON users.id = orders.user_id
INNER JOIN products
ON orders.product = products.name;

このようなテーブルになリます。

name	product	category	price
太郎	りんご	フルーツ	150
花子	バナナ	フルーツ	100
太郎	オレンジ	フルーツ	80

外部結合（左結合、右結合、完全外部結合）

https://wa3.i-3-i.info/diff523db.html

外部結合の基本的な構文は以下の通りです。
FROMで指定したテーブルが左テーブルになります。

SELECT 列名
FROM テーブル1
LEFT/RIGHT/FULL OUTER JOIN テーブル2
ON テーブル1.キーとなる列名 = テーブル2.キーとなる列名;

LEFT JOIN（左結合）

左結合は、左側のテーブルのすべての行と、右側のテーブルのマッチする行を結合します。

SELECT users.name, orders.product, orders.price
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;

LEFT JOINは、左側のテーブル（usersテーブル）のすべての行を保持し、それぞれの行に対して右側のテーブル（ordersテーブル）の該当する行があればそれらを結合します。
該当する行がない場合は、右側のテーブルのカラムにはNULLが入ります。

具体的には、太郎（idが1）と花子（idが2）に対応する注文がordersテーブルにありますが、次郎（idが3）には注文がありません。
そのため、太郎と花子の情報は注文情報と一緒に表示されますが、次郎の情報は注文情報がないためNULLが入ります。

name	product	price
太郎	りんご	150
太郎	オレンジ	80
花子	バナナ	100
次郎	NULL	NULL

RIGHT JOIN（右結合）

右結合は、左側のテーブルのマッチする行と、右側のテーブルのすべての行を結合します。

SELECT users.name, orders.product, orders.price
FROM users
RIGHT JOIN orders
ON users.id = orders.user_id;

name	product	price
太郎	りんご	150
花子	バナナ	100
太郎	オレンジ	80

FULL OUTER JOIN（完全外部結合）

FULL OUTER JOINは、左右のテーブルのすべての行を保持し、該当する行がない場合はNULLで埋めます。

SELECT users.name, orders.product, orders.price
FROM users
FULL OUTER JOIN orders
ON users.id = orders.user_id;

太郎と花子には注文がありますが、次郎には注文がありません。そのため、次郎の行はordersテーブルに該当する行がないため、productとpriceの列にはNULLが入ります

また、ordersテーブルには次郎以外にuser_idが3の注文がないため、次郎の行以外はproductとpriceの列にはNULLが入ります。

name	product	price
太郎	りんご	150
花子	バナナ	100
太郎	オレンジ	80
次郎	NULL	NULL

その他の結合

CROSS JOIN（交差結合）

CROSS JOINを使用すると、結合条件なしで1つのテーブルの各行がもう1つのテーブルのすべての行と組み合わされます。

つまり、結果の行数は最初のテーブルの行数と2番目のテーブルの行数の積となります。

SELECT users.name, orders.product, orders.price
FROM users
CROSS JOIN orders;

name	product	price
太郎	りんご	150
太郎	バナナ	100
太郎	オレンジ	80
花子	りんご	150
花子	バナナ	100
花子	オレンジ	80
次郎	りんご	150
次郎	バナナ	100
次郎	オレンジ	80

NATURAL（自然結合）

項目名を指定しなくても、２つのテーブルの同じ名前の項目を結合してデータを取得します。

練習問題

下記の場合に顧客の名前とその顧客が行った注文の合計金額を取得してください。

customersテーブル:

id (顧客ID)
name (顧客名)
email (メールアドレス)

CREATE TABLE customers(
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(254)
);

INSERT INTO customers(id,name,email)
VALUES
(1,'太郎','aa.com'),
(2,'花子','bb.com');

ordersテーブル:
id (注文ID)
customer_id (顧客ID、customersテーブルのidと関連付け)
product (購入商品)
quantity (数量)
price (合計金額)

CREATE TABLE orders(
id INT PRIMARY KEY,
customer_id INT,
product VARCHAR(50),
quantity INT,
price INT
);

INSERT INTO orders(id , customer_id , product , quantity , price )
VALUES
(1,1,'商品A',10,100),
(2,2,'商品B',50,300),
(3,2,'商品C',10000,22222);

解答

SELECT customers.name,orders.quantity
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;

上記では次のようになり、数量が合計できていません。

name	quantity
太郎	10
花子	50
花子	10000

SUM関数を使用して指定された列の値を合計し、その合計値でall_totalという新しい列を作成しています。
GROUP BY句を使用して指定された列の値に基づいて行をグループ化しています。
今回は、customersテーブルのid列に基づいて顧客をグループ化しています。したがって、各顧客の注文総数を計算し、顧客ごとに1行の結果を返すようにクエリが構成されます。

SELECT customers.name, SUM(orders.quantity) AS all_total
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id
GROUP BY customers.id;

name	quantity
太郎	10
花子	10050

練習2

さらにordersテーブル構造を変更し、productsテーブルを追加して、顧客ごとに注文された商品の詳細情報（商品名、数量、合計金額）を取得してください。

まず、ordersテーブルのproduct列を削除し、新しいproduct_id列を追加します。

ALTER TABLE orders
DROP COLUMN product,
ADD COLUMN product_id INT;

ordersテーブルの構造は下記になります。
id (注文ID)
customer_id (顧客ID、customersテーブルのidと関連付け)
quantity (数量)
total_amount (合計金額)
product_id (商品ID、productsテーブルのidと関連付け)

ですが、このままでは新しいproduct_id列はNULLになっているので商品IDの値を追加します。

UPDATE orders
SET product_id = 
    CASE 
        WHEN id = 1 THEN 1  
        WHEN id = 2 THEN 2  
        WHEN id = 3 THEN 3  
        ELSE NULL
    END;

productsテーブル:
id (商品ID)
name (商品名)
price (価格)

CREATE TABLE products(
id INT PRIMARY KEY,
name VARCHAR(50),
price INT
);

INSERT INTO orders(id , name ,price )
VALUES
(1,'商品A',100),
(2,'商品B',200),
(3,'商品C',300);

INSERT INTO orders(id , customer_id , product , quantity , price )
VALUES
(1,1,'商品A',10,100),
(2,2,'商品B',50,300),
(3,2,'商品C',10000,22222);

解答2

SELECT customers.name AS customer_name, products.name AS product_name, orders.quantity, orders.price
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN products ON orders.product_id = products.id;

customer_name	product_name	quantity	price
太郎	商品A	10	100
花子	商品B	50	300
花子	商品C	10000	22222

外部キー制約

外部キー制約とは、データベースのテーブル間の関連性を確立し、維持するための規則です。

具体的には、あるテーブルの列（外部キー）が別のテーブルの列（主キー）を参照し、関連付けられたデータが整合性を持つことを保証します。

練習問題を例にすると、顧客テーブル（customers）と注文テーブル（orders）があります。顧客テーブルには顧客の情報が格納されており、注文テーブルには注文の情報が格納されています。注文テーブルの中には、顧客ID（customer_id）という列がありますが、これは顧客テーブルのID列と関連付けられています。

外部キー制約を使用すると、注文テーブルのcustomer_id列が、顧客テーブルのID列の値を参照することができます。つまり、注文テーブルに新しい注文を追加する際には、customer_id列に存在する顧客IDのみが使用されます。存在しない顧客IDが挿入されることはありません。

これにより、データの整合性が保たれ、注文が実際の顧客に関連付けられていることが保証されます。外部キー制約に違反する操作が行われた場合（たとえば、存在しない顧客IDを指定した注文が挿入された場合）、エラーが発生し、その操作が拒否されます。

外部キー制約の付与

ordersテーブルに外部キー制約を付与してみます。

customer_id列に対する外部キー制約を追加します。

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(id);

• ALTER TABLE orders: ordersテーブルに変更を加えることを宣言しています。

• ADD CONSTRAINT fk_customer: 新しい制約をordersテーブルに追加します。この外部キー制約には、fk_customerという名前を付けています。

• FOREIGN KEY (customer_id): 外部キー制約が適用される列を指定します。ここではcustomer_id列に外部キー制約を適用しています。

• REFERENCES customers(id): 外部キーが参照するテーブルと列を指定します。customer_id列はcustomersテーブルのid列を参照します。つまり、customer_id列に保存される値はcustomersテーブルのid列に存在する必要があります。

product_id列に対する外部キー制約を追加します。

ALTER TABLE orders
ADD CONSTRAINT fk_product
FOREIGN KEY (product_id)
REFERENCES products(id);

外部キー制約に違反する操作をテストしてみます。

INSERT INTO orders (id, customer_id, quantity, price, product_id) 
VALUES (1, 9999, 10, 100, 1);

下記が表示されると思います。エラーコード1062は、一意制約に違反したことを示しています。

Error Code: 1062. Duplicate entry '1' for key 'orders.PRIMARY'

外部キー制約を確認する方法

下記を実行してください。

SHOW CREATE TABLE orders;

統合結合（UNION / UNION ALL）

複数のクエリ結果（SELECT句）を一つの結果セットとして結合する方法です。
主にUNIONとUNION ALLの2つの方法があります。

基本的な構文は以下です。

SELECT句
UNION／UNION ALL
SELECT句;

UNION

UNIONは、複数のSELECT文から返される結果を結合し、重複する行を取り除いてユニークな結果のみを返します。

練習問題のordersテーブルからproduct_idが1の注文とproduct_idが2の注文を結合しています。

SELECT customer_id, product_id, quantity, price
FROM orders
WHERE product_id = 1

UNION

SELECT customer_id, product_id, quantity, price
FROM orders
WHERE product_id = ２;

customer_id	product_id	quantity	price
1	1	10	100
2	2	50	300

UNION ALL

UNION ALLは、複数のSELECT文から返されるすべての行を重複を排除せずに結合します。
UNIONの例であれば、customer_id、product_id、quantity、priceが完全に同じ行が複数回現れる場合、それらは重複するレコードと見なされます。

よって、今回の場合はUNIONと同じ結果になります。

SELECT customer_id, product_id, quantity, price
FROM orders
WHERE product_id = 1

UNION ALL

SELECT customer_id, product_id, quantity, price
FROM orders
WHERE product_id = ２;

customer_id	product_id	quantity	price
1	1	10	100
2	2	50	300

メインクエリ

メインクエリは、データベースから情報を取得するための主要なクエリです。

通常、メインクエリは結果の集合を返し、サブクエリを含むことがあります。

サブクエリ

サブクエリは、SQLクエリの中に含まれる別のクエリのことを指します。
メインクエリの条件や結果に基づいて、より詳細な情報を取得するために使用されます。

メインクエリでは、customersテーブルから顧客名を選択し、サブクエリ（(SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) AS order_count
）でordersテーブルから顧客ごとの注文数を数えています。

SELECT customers.name, 
       (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) AS order_count
FROM customers;

name	order_count
太郎	1
花子	2

メインクエリでは、productsテーブルから商品名を選択し、サブクエリでは、ordersテーブルから商品ごとの平均注文数量を求めています。

SELECT products.name, 
       ROUND((SELECT AVG(orders.quantity) FROM orders WHERE orders.product_id = products.id), 1) AS average_quantity
FROM products;

name	average_quantity
商品A	10.0
商品B	50.0
商品C	10000.0

メインクエリでは、ordersテーブルとcustomersテーブルを結合して、各注文の顧客名と注文金額を取得しています。サブクエリでは、ordersテーブル内のすべての注文の中で最高価格を取得しています。

そして、メインクエリのWHERE句で、この最高価格と一致する注文をフィルタリングしています。したがって、このクエリーにより、最高価格の注文を行った顧客とその注文金額が取得されます。

SELECT customers.name AS customer_name,
       orders.price
FROM orders
JOIN customers  ON orders.customer_id = customers.id
WHERE orders.price = (
    SELECT MAX(orders2.price)
    FROM orders orders2
);

メインクエリとサブクエリが同じテーブルを参照している場合、サブクエリ内でメインクエリと同じテーブルを参照する必要があります。エイリアスを使用してサブクエリのテーブルを指定することで、正しいカラムが参照されます。

つまり、orders.priceはメインクエリのテーブルordersのカラムを参照していますが、サブクエリ内ではordersテーブルのエイリアスであるorders2を使用する必要があります。

customer_name	price
花子	22222

テーブルのエイリアス

基本構文です。ちなみに、ASキーワードは省略しても問題ありません。

SELECT カラム名
FROM テーブル名 AS エイリアス名

先ほどの顧客ごとの注文数を取得するの例では下記のように記述することができます。

SELECT c.name, 
       (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count
FROM customers c;

終わりに

何かありましたらお気軽にコメント等いただけると助かります。
ここまでお読みいただきありがとうございます🎉