SQL 第2版 ゼロからはじめるデータベース操作 アウトプット（第10回）

## 概要
- 本記事は[SQL 第2版 ゼロからはじめるデータベース操作](https://www.shoeisha.co.jp/book/detail/9784798144450)で学んだ内容をまとめたものです。
  - 前回は「**第6章：関数、述語、CASE式**」 で学んだ内容をまとめました：[SQL 第2版 ゼロからはじめるデータベース操作 アウトプット（第9回）](https://qiita.com/takumi_links/items/5c55dbcf4d0bdac570ee)
  - 今回は「**第7章：集合演算**」についてまとめていきます！

### 集合演算とは
- レコードの集合
  - 集合演算は、レコード同士を足したり引いたり、掛けたり割ったりする演算のこと（レコードの四則演算）

#### UNION
- レコードの足し算
  - 2つのテーブルのレコードを結合する
  - 集合演算子は「**重複を排除する**」ため、同じレコードが複数存在しても1つにまとめられる

- UNIONの注意点
  - 演算対象となるレコードの列数は同じでなければならない
  - 足し算対象となるレコードの列のデータ型は同じでなければならない
  - ORDER BY句はUNIONの後に指定する

```sql
mysql> CREATE TABLE Shohin2
    -> (shohin_id     CHAR(4)      NOT NULL,
    ->  shohin_mei    VARCHAR(100) NOT NULL,
    ->  shohin_bunrui VARCHAR(32)  NOT NULL,
    ->  hanbai_tanka  INTEGER      ,
    ->  shiire_tanka  INTEGER      ,
    ->  torokubi      DATE         ,
    ->  PRIMARY KEY (shohin_id));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO Shohin2 VALUES ('0001', 'Tシャツ' ,'衣服', 1000, 500, '2009-09-20')
Query OK, 1 rows affected (0.06 sec)
mysql> INSERT INTO Shohin2 VALUES ('0002', '穴あけパンチ', '事務用品', 500, 320, '2009-09-11')
Query OK, 1 rows affected (0.06 sec)
mysql> INSERT INTO Shohin2 VALUES ('0003', 'カッターシャツ', '衣服', 4000, 2800, NULL)
Query OK, 1 rows affected (0.06 sec)
mysql> INSERT INTO Shohin2 VALUES ('0009', '手袋', '衣服', 800, 500, NULL)
Query OK, 1 rows affected (0.06 sec)
mysql> INSERT INTO Shohin2 VALUES ('0010', 'やかん', 'キッチン用品', 2000, 1700, '2009-09-20')
Query OK, 1 rows affected (0.06 sec)
mysql> COMMIT

mysql> SELECT shohin_id, shohin_mei FROM Shohin UNION SELECT shohin_id, shohin_mei FROM Shohin2;
+-----------+-----------------------+
| shohin_id | shohin_mei            |
+-----------+-----------------------+
| 0001      | Tシャツ               |
| 0002      | 穴あけパンチ          |
| 0003      | カッターシャツ        |
| 0004      | 包丁                  |
| 0005      | 圧力鍋                |
| 0006      | フォーク              |
| 0007      | おろしがね            |
| 0008      | ボールペン            |
| 0009      | 手袋                  |
| 0010      | やかん                |
+-----------+-----------------------+
10 rows in set (0.01 sec)

-- 列の数が違う場合はエラーになる
mysql> SELECT shohin_id, shohin_mei FROM Shohin UNION SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin2;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

-- Mysqlでは、型が異なっていても、暗黙的に型変換される
mysql> SELECT shohin_id, hanbai_tanka FROM Shohin UNION SELECT shohin_id,shohin_mei FROM Shohin2;
+-----------+-----------------------+
| shohin_id | hanbai_tanka          |
+-----------+-----------------------+
| 0001      | 1000                  |
| 0002      | 500                   |
| 0003      | 4000                  |
| 0004      | 3000                  |
| 0005      | 6800                  |
| 0006      | 500                   |
| 0007      | 880                   |
| 0008      | 100                   |
| 0001      | Tシャツ               |
| 0002      | 穴あけパンチ          |
| 0003      | カッターシャツ        |
| 0009      | 手袋                  |
| 0010      | やかん                |
+-----------+-----------------------+
13 rows in set (0.01 sec)

-- ORDER BY句はUNIONの後に指定する
mysql> SELECT shohin_id, shohin_mei FROM Shohin WHERE torokubi = '2009-09-20'
  -> UNION
       -> SELECT shohin_id, shohin_mei FROM Shohin2 WHERE torokubi = '2009-09-20'
  -> ORDER BY shohin_id;
+-----------+--------------+
| shohin_id | shohin_mei   |
+-----------+--------------+
| 0001      | Tシャツ      |
| 0004      | 包丁         |
| 0006      | フォーク     |
| 0010      | やかん       |
+-----------+--------------+
4 rows in set (0.00 sec)
```

#### ALLオプション
- UNION ALLを使うと、**重複を排除せずにレコードを結合する**

```sql
mysql> SELECT shohin_id, shohin_mei FROM Shohin
    -> UNION ALL
    -> SELECT shohin_id, shohin_mei FROM Shohin2;
+-----------+-----------------------+
| shohin_id | shohin_mei            |
+-----------+-----------------------+
| 0001      | Tシャツ               |
| 0002      | 穴あけパンチ          |
| 0003      | カッターシャツ        |
| 0004      | 包丁                  |
| 0005      | 圧力鍋                |
| 0006      | フォーク              |
| 0007      | おろしがね            |
| 0008      | ボールペン            |
| 0001      | Tシャツ               |
| 0002      | 穴あけパンチ          |
| 0003      | カッターシャツ        |
| 0009      | 手袋                  |
| 0010      | やかん                |
+-----------+-----------------------+
13 rows in set (0.00 sec)
```

#### INTERSECT
- レコードの掛け算
  - 2つのテーブルのレコードを結合し、両方に存在するレコードのみを返す

```sql
mysql> SELECT shohin_id, shohin_mei FROM Shohin 
    -> INTERSECT
    -> SELECT shohin_id, shohin_mei FROM Shohin2;
+-----------+-----------------------+
| shohin_id | shohin_mei            |
+-----------+-----------------------+
| 0001      | Tシャツ               |
| 0002      | 穴あけパンチ          |
| 0003      | カッターシャツ        |
+-----------+-----------------------+
3 rows in set (0.01 sec)

mysql> SELECT shohin_id, shohin_mei FROM Shohin
    -> INTERSECT ALL
    -> SELECT shohin_id, shohin_mei FROM Shohin2;
+-----------+-----------------------+
| shohin_id | shohin_mei            |
+-----------+-----------------------+
| 0001      | Tシャツ               |
| 0002      | 穴あけパンチ          |
| 0003      | カッターシャツ        |
+-----------+-----------------------+
3 rows in set (0.00 sec)
```

#### EXCEPT
- レコードの引き算
  - 1つ目のテーブルから2つ目のテーブルに存在しないレコードを返す

```sql
mysql> SELECT shohin_id, shohin_mei FROM Shohin
  -> EXCEPT
       -> SELECT shohin_id, shohin_mei FROM Shohin2;
+-----------+-----------------+
| shohin_id | shohin_mei      |
+-----------+-----------------+
| 0004      | 包丁            |
| 0005      | 圧力鍋          |
| 0006      | フォーク        |
| 0007      | おろしがね      |
| 0008      | ボールペン      |
+-----------+-----------------+
5 rows in set (0.01 sec)
              
mysql> SELECT shohin_id, shohin_mei FROM Shohin2
  -> EXCEPT
       -> SELECT shohin_id, shohin_mei FROM Shohin;
+-----------+------------+
| shohin_id | shohin_mei |
+-----------+------------+
| 0009      | 手袋       |
| 0010      | やかん     |
+-----------+------------+
2 rows in set (0.00 sec)
```

### 結合
- テーブルを列方向に連結する
  - 別のテーブルから列を持ってきて「列を増やす」操作
  - 結合は、テーブル同士の関連性を利用してデータを取得するために使用される

#### INNER JOIN
- 内部結合
  - 2つのテーブルの共通する列を基に、両方に存在するレコードのみを返す

```sql
mysql> SELECT TS.tenpo_id, tenpo_mei, S.shohin_id, S.shohin_mei, S.hanbai_tanka FROM TenpoShohin AS TS INNER JOIN Shohin AS S ON TS.shohin_id = S.shohin_id;
+----------+-----------+-----------+-----------------------+--------------+
| tenpo_id | tenpo_mei | shohin_id | shohin_mei            | hanbai_tanka |
+----------+-----------+-----------+-----------------------+--------------+
| 000A     | 東京      | 0001      | Tシャツ               |         1000 |
| 000A     | 東京      | 0002      | 穴あけパンチ          |          500 |
| 000A     | 東京      | 0003      | カッターシャツ        |         4000 |
| 000B     | 名古屋    | 0002      | 穴あけパンチ          |          500 |
| 000B     | 名古屋    | 0003      | カッターシャツ        |         4000 |
| 000B     | 名古屋    | 0004      | 包丁                  |         3000 |
| 000B     | 名古屋    | 0006      | フォーク              |          500 |
| 000B     | 名古屋    | 0007      | おろしがね            |          880 |
| 000C     | 大阪      | 0003      | カッターシャツ        |         4000 |
| 000C     | 大阪      | 0004      | 包丁                  |         3000 |
| 000C     | 大阪      | 0006      | フォーク              |          500 |
| 000C     | 大阪      | 0007      | おろしがね            |          880 |
| 000D     | 福岡      | 0001      | Tシャツ               |         1000 |
+----------+-----------+-----------+-----------------------+--------------+
13 rows in set (0.00 sec)

mysql> SELECT TS.tenpo_id, tenpo_mei, S.shohin_id, S.shohin_mei, S.hanbai_tanka FROM TenpoShohin AS TS
  -> INNER JOIN Shohin AS S ON TS.shohin_id = S.shohin_id
                        -> WHERE TS.tenpo_id = '000A';
+----------+-----------+-----------+-----------------------+--------------+
| tenpo_id | tenpo_mei | shohin_id | shohin_mei            | hanbai_tanka |
+----------+-----------+-----------+-----------------------+--------------+
| 000A     | 東京      | 0001      | Tシャツ               |         1000 |
| 000A     | 東京      | 0002      | 穴あけパンチ          |          500 |
| 000A     | 東京      | 0003      | カッターシャツ        |         4000 |
+----------+-----------+-----------+-----------------------+--------------+
3 rows in set (0.00 sec)
```

#### 外部結合
- 片方のテーブルの情報が全て出力される
  - `LEFT JOIN` は、左側のテーブルにある行をすべて出力し、右側に一致するデータがあれば表示、なければ NULL を出力
  - `RIGHT JOIN` は、右側のテーブルにある行をすべて出力し、左側に一致するデータがあれば表示、なければ NULL を出力

```sql
mysql> SELECT TS.tenpo_id, tenpo_mei, S.shohin_id, S.shohin_mei FROM Shohin AS S LEFT JOIN TenpoShohin AS TS ON TS.shohin_id = S.shohin_id;
+----------+-----------+-----------+-----------------------+
| tenpo_id | tenpo_mei | shohin_id | shohin_mei            |
+----------+-----------+-----------+-----------------------+
| 000D     | 福岡      | 0001      | Tシャツ               |
| 000A     | 東京      | 0001      | Tシャツ               |
| 000B     | 名古屋    | 0002      | 穴あけパンチ          |
| 000A     | 東京      | 0002      | 穴あけパンチ          |
| 000C     | 大阪      | 0003      | カッターシャツ        |
| 000B     | 名古屋    | 0003      | カッターシャツ        |
| 000A     | 東京      | 0003      | カッターシャツ        |
| 000C     | 大阪      | 0004      | 包丁                  |
| 000B     | 名古屋    | 0004      | 包丁                  |
| NULL     | NULL      | 0005      | 圧力鍋                |
| 000C     | 大阪      | 0006      | フォーク              |
| 000B     | 名古屋    | 0006      | フォーク              |
| 000C     | 大阪      | 0007      | おろしがね            |
| 000B     | 名古屋    | 0007      | おろしがね            |
| NULL     | NULL      | 0008      | ボールペン            |
+----------+-----------+-----------+-----------------------+
15 rows in set (0.00 sec)
```

#### CROSS JOIN
- クロス結合
  - 2つのテーブルの全ての組み合わせを返す
  - 結果は、左側のテーブルの行数 × 右側のテーブルの行数 のレコードが生成される

```sql
mysql> SELECT TS.tenpo_id, TS.tenpo_mei, S.shohin_id, S.shohin_mei FROM Shohin AS S CROSS JOIN TenpoShohin AS TS;
+----------+-----------+-----------+-----------------------+
| tenpo_id | tenpo_mei | shohin_id | shohin_mei            |
+----------+-----------+-----------+-----------------------+
| 000A     | 東京      | 0008      | ボールペン            |
| 000A     | 東京      | 0007      | おろしがね            |
| 000A     | 東京      | 0006      | フォーク              |
| 000A     | 東京      | 0005      | 圧力鍋                |
・
・
・
| 000D     | 福岡      | 0002      | 穴あけパンチ          |
| 000D     | 福岡      | 0001      | Tシャツ               |
+----------+-----------+-----------+-----------------------+
104 rows in set (0.00 sec)
```

### 練習問題を解いてみる
- 7.1 - 7.2を解いてみた内容を掲載

#### 7.1
- クエリの実行

```sql
-- 同じテーブルをUNIONで結合し、重複を排除して全てのレコードを取得
-- INTERSECTを使って、同じテーブルから重複するレコードを取得
-- 結果としては、Shohinテーブルの全てのレコードが返される
mysql> SELECT * FROM Shohin
  -> UNION
       -> SELECT * FROM Shohin
  -> INTERSECT
          -> SELECT * FROM Shohin
  -> ORDER BY shohin_id;
+-----------+-----------------------+--------------------+--------------+--------------+------------+
| shohin_id | shohin_mei            | shohin_bunrui      | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------------+--------------------+--------------+--------------+------------+
| 0001      | Tシャツ               | 衣服               |         1000 |          500 | 2009-09-20 |
| 0002      | 穴あけパンチ          | 事務用品           |          500 |          320 | 2009-09-11 |
| 0003      | カッターシャツ        | 衣服               |         4000 |         2800 | NULL       |
| 0004      | 包丁                  | キッチン用品       |         3000 |         2800 | 2009-09-20 |
| 0005      | 圧力鍋                | キッチン用品       |         6800 |         5000 | 2009-01-15 |
| 0006      | フォーク              | キッチン用品       |          500 |         NULL | 2009-09-20 |
| 0007      | おろしがね            | キッチン用品       |          880 |          790 | 2008-04-28 |
| 0008      | ボールペン            | 事務用品           |          100 |         NULL | 2009-11-11 |
+-----------+-----------------------+--------------------+--------------+--------------+------------+
8 rows in set (0.01 sec)

mysql> SELECT * FROM Shohin INTERSECT SELECT * FROM Shohin ORDER BY shohin_id;
+-----------+-----------------------+--------------------+--------------+--------------+------------+
| shohin_id | shohin_mei            | shohin_bunrui      | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------------+--------------------+--------------+--------------+------------+
| 0001      | Tシャツ               | 衣服               |         1000 |          500 | 2009-09-20 |
| 0002      | 穴あけパンチ          | 事務用品           |          500 |          320 | 2009-09-11 |
| 0003      | カッターシャツ        | 衣服               |         4000 |         2800 | NULL       |
| 0004      | 包丁                  | キッチン用品       |         3000 |         2800 | 2009-09-20 |
| 0005      | 圧力鍋                | キッチン用品       |         6800 |         5000 | 2009-01-15 |
| 0006      | フォーク              | キッチン用品       |          500 |         NULL | 2009-09-20 |
| 0007      | おろしがね            | キッチン用品       |          880 |          790 | 2008-04-28 |
| 0008      | ボールペン            | 事務用品           |          100 |         NULL | 2009-11-11 |
+-----------+-----------------------+--------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)
```

#### 7-2
- 外部結合

```sql
mysql> SELECT TS.tenpo_id, tenpo_mei, S.shohin_id, S.shohin_mei FROM Shohin AS S LEFT JOIN TenpoShohin AS TS ON TS.shohin_id = S.shohin_id ORDER BY TS.tenpo_id ASC;
+----------+-----------+-----------+-----------------------+
| tenpo_id | tenpo_mei | shohin_id | shohin_mei            |
+----------+-----------+-----------+-----------------------+
| NULL     | NULL      | 0005      | 圧力鍋                |
| NULL     | NULL      | 0008      | ボールペン            |
| 000A     | 東京      | 0001      | Tシャツ               |
| 000A     | 東京      | 0002      | 穴あけパンチ          |
| 000A     | 東京      | 0003      | カッターシャツ        |
| 000B     | 名古屋    | 0002      | 穴あけパンチ          |
| 000B     | 名古屋    | 0003      | カッターシャツ        |
| 000B     | 名古屋    | 0004      | 包丁                  |
| 000B     | 名古屋    | 0006      | フォーク              |
| 000B     | 名古屋    | 0007      | おろしがね            |
| 000C     | 大阪      | 0003      | カッターシャツ        |
| 000C     | 大阪      | 0004      | 包丁                  |
| 000C     | 大阪      | 0006      | フォーク              |
| 000C     | 大阪      | 0007      | おろしがね            |
| 000D     | 福岡      | 0001      | Tシャツ               |
+----------+-----------+-----------+-----------------------+
15 rows in set (0.00 sec)

-- COALESCE: NULLを別の値に置き換える
mysql> SELECT COALESCE(TS.tenpo_id, '不明') AS tenpo_id, COALESCE(TS.tenpo_mei,'不明') AS tenpo_id, S.shohin_id, S.shohin_mei, S.hanbai_tanka FROM Shohin AS S LEFT JOIN TenpoShohin AS TS ON TS.shohin_id = S.shohin_id ORDER BY
  TS.tenpo_id ASC;
+----------+-----------+-----------+-----------------------+--------------+
| tenpo_id | tenpo_id  | shohin_id | shohin_mei            | hanbai_tanka |
+----------+-----------+-----------+-----------------------+--------------+
| 不明    | 不明     | 0005      | 圧力鍋                |         6800 |
| 不明    | 不明     | 0008      | ボールペン            |          100 |
| 000A     | 東京      | 0001      | Tシャツ               |         1000 |
| 000A     | 東京      | 0002      | 穴あけパンチ          |          500 |
| 000A     | 東京      | 0003      | カッターシャツ        |         4000 |
| 000B     | 名古屋    | 0002      | 穴あけパンチ          |          500 |
| 000B     | 名古屋    | 0003      | カッターシャツ        |         4000 |
| 000B     | 名古屋    | 0004      | 包丁                  |         3000 |
| 000B     | 名古屋    | 0006      | フォーク              |          500 |
| 000B     | 名古屋    | 0007      | おろしがね            |          880 |
| 000C     | 大阪      | 0003      | カッターシャツ        |         4000 |
| 000C     | 大阪      | 0004      | 包丁                  |         3000 |
| 000C     | 大阪      | 0006      | フォーク              |          500 |
| 000C     | 大阪      | 0007      | おろしがね            |          880 |
| 000D     | 福岡      | 0001      | Tシャツ               |         1000 |
+----------+-----------+-----------+-----------------------+--------------+
15 rows in set (0.00 sec)
```

### まとめ
- 本記事では「**第7章：集合演算**」 についてまとめました！
- 次回は「**第8章：SQLで高度な処理を行なう**」 についてまとめていきます！

## 参考
- [SQL 第2版 ゼロからはじめるデータベース操作](https://www.shoeisha.co.jp/book/detail/9784798144450)