【SQL】AWS Athena データ抽出入門 Vol.1【初心者向け】

# 内容

[AWS Athena](https://aws.amazon.com/jp/athena/?whats-new-cards.sort-by=item.additionalFields.postDateTime&whats-new-cards.sort-order=desc)へ用意されているテーブルのデータを、SQLを使って取得できるよう目指す方向け「入門編」です。

その他ボリュームページ
- [【SQL】AWS Athena データ抽出入門 Vol.2【初心者向け】](https://qiita.com/nijigen_plot/items/98580788a7f87535bba7)
- [【SQL】AWS Athena データ抽出入門 Vol.3【初心者向け】](https://qiita.com/nijigen_plot/items/7a48e4c015cef67691a1)


## 公式説明書

[Athena エンジンバージョン2](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/presto-functions.html)は [Presto 0.217](https://prestodb.io/docs/0.217/index.html) に基づいています。
Athenaへ用意されているテーブルからデータを取得するSQLが書けるようになるには、[6. Funcsiont and Operators](https://prestodb.io/docs/0.217/functions.html)のページが参考になります。

# SQL基本操作

SQLは表で管理しているデータから、該当データを抽出して取得できます。

以下のような表Aがあるとします。

|商品コード |商品名 | 単価|
|--- | ---|--- |
|001 | 消しゴム| 80|
| 002|鉛筆 |70 |
|003 |筆箱 | 750|
|004|ボールペン | 120|
|005 |電卓 | 780|
|006 | 三角定規| 200|




SQLは以下のような形でデータを取得できます。

```sql
SELECT 列名 FROM 表名 WHERE 条件
```

## 列を取り出す(SELECT)

SELECT句は**どの列を取り出すか**を決めることができます。
表Aから「商品コード」の列を取り出したい場合、`SELECT 商品コード`となります。

（結果はこんな感じ）
|商品コード |
|--- | 
|001 | 
| 002|
|003 |
|004|
|005 |
|006 |


全ての列を取り出したい場合、`SELECT *` と書きます。

## 表を選択する(FROM)

FROM句ではどの表を対象とするかを選択することができます。
今回は表Aが対象ですので、`FROM 表A`となります。

## 行を選択する(WHERE)

WHERE句は特定の条件に合致する行を取り出すことができます。
表Aから「単価が100未満」の行を取り出したい場合、`WHERE 単価 < 100`となります。

（結果はこんな感じ）
|商品コード |商品名 | 単価|
|--- | ---|--- |
|001 | 消しゴム| 80|
| 002|鉛筆 |70 |


データを操作する基本の句として

- SELECT
- FROM
- WHERE

の3つがあります。
以降は、オリジナルのデータを使って解説していきます。

# 練習用データ

Athenaで以下のクエリを「**実行**」すると、テーブルを作ることができます。
このテーブルデータを使って、以降は解説しますので、Athenaのクエリ実行環境がある方は、是非作って試してみてください。

## テーブル1(practice_user)

```sql
CREATE TABLE practice_user AS
SELECT
    user_id,
    sex,
    age,
    pref,
    cast(created_at as timestamp) as created_at
FROM (
    VALUES
        (1, '女性', '32', '東京都','2021-10-06 12:20:45'),
        (2, '男性', '26', '青森県','2021-10-24 14:47:20'),
        (3, '女性', '49', '岐阜県','2021-11-18 02:50:33'),
        (4, '女性', '48', '東京都','2021-09-03 01:11:07'),
        (5, '男性', '20', '大阪府','2021-08-30 01:43:22'),
        (6, '男性', '70', '京都府','2021-08-12 21:15:59'),
        (7, '女性', '不明', '東京都','2021-09-03 03:38:01'),
        (8, '女性', '18', '石川県','2021-08-16 18:40:18'),
        (9, '男性', '22', '山口県','2021-12-21 08:55:13'),
        (10, '女性', '53', '鹿児島県','2021-08-13 20:37:22')
) AS t (user_id, sex, age, pref, created_at)
```

Athenaコンソールプレビュー結果
![SnapCrab_NoName_2022-4-7_22-20-9_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/2ffca188-2a6a-e496-1fb7-99b54464bb2b.png)



## テーブル2(practice_product)

```sql
CREATE TABLE practice_product AS
SELECT
    item_id,
    product_name,
    unit_price,
    product_group
FROM (
    VALUES
        ('001', '消しゴム', 80, 0),
        ('002', '鉛筆', 70, 0),
        ('003', '筆箱', 750, 1),
        ('004', 'ボールペン', 120, 0),
        ('005', '電卓', 780, 1),
        ('006', '三角定規', 200, 1),
        ('007', 'ノート', 180, 0),
        ('008', 'コンパス', 600, 1),
        ('009', '下敷き', 320, 0),
        ('010', 'シャーペン', 150, 0)
) AS t (item_id, product_name, unit_price, product_group)
```

Athenaコンソールプレビュー結果
![SnapCrab_NoName_2022-4-7_0-44-9_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/3a65b977-3d6a-ec36-b6a6-0813ba449b75.png)



## テーブル3(practice_sales)

```sql
CREATE TABLE practice_sales AS
SELECT
    sale_id,
    cast(sales_at as timestamp) as sales_at,
    substr(sales_at,1,10) as sales_date,
    item_id,
    quantity,
    user_id
FROM (
    VALUES
        (0,'2022-02-23 22:41:24','006',4, 8),
        (1,'2021-09-20 17:30:11','001',4, 5),
        (2,'2022-01-28 17:48:15','010',5, 3),
        (3,'2021-09-22 23:59:06','009',3, 4),
        (4,'2022-01-19 04:41:30','010',3, 2),
        (5,'2021-08-21 05:27:01','007',1, 10),
        (6,'2021-09-22 14:43:39','003',5, 4),
        (7,'2021-11-29 03:45:11','010',4, 3),
        (8,'2021-11-04 13:03:38','007',5, 1),
        (9,'2022-02-15 05:38:25','001',4, 10),
        (10,'2022-02-03 17:39:21','009',4, 3),
        (11,'2022-01-26 06:57:37','005',1, 9),
        (12,'2021-11-27 13:56:16','002',1, 7),
        (13,'2021-08-26 21:59:46','004',1, 8),
        (14,'2021-11-12 13:55:27','009',4, 2),
        (15,'2021-12-22 05:27:00','001',3, 5),
        (16,'2022-03-12 14:23:05','009',5, 4),
        (17,'2021-08-20 13:17:50','006',5, 10),
        (18,'2021-09-17 16:55:10','010',3, 5),
        (19,'2021-09-20 16:55:53','005',3, 7),
        (20,'2022-02-23 18:09:06','007',3, 8),
        (21,'2021-09-13 20:33:34','004',1, 10),
        (22,'2022-02-22 14:21:04','001',4, 9),
        (23,'2022-03-05 02:18:10','009',5, 10),
        (24,'2022-01-13 19:43:05','005',4, 4),
        (25,'2021-11-29 20:24:37','010',4, 3),
        (26,'2021-09-06 04:58:59','006',3, 4),
        (27,'2021-12-26 02:54:35','006',2, 7),
        (28,'2021-11-23 14:32:08','006',3, 4),
        (29,'2021-10-26 07:44:13','009',4, 5),
        (30,'2021-09-22 06:00:45','009',5, 10),
        (31,'2021-10-27 10:54:27','009',4, 6),
        (32,'2021-11-14 08:48:17','005',3, 5),
        (33,'2022-01-31 17:15:35','009',4, 4),
        (34,'2021-09-15 03:41:44','006',1, 4),
        (35,'2022-01-27 05:43:28','003',5, 8),
        (36,'2021-12-13 06:00:52','001',3, 8),
        (37,'2022-03-28 22:45:28','004',4, 2),
        (38,'2021-10-25 01:48:08','007',1, 6),
        (39,'2022-02-22 17:28:21','006',1, 6),
        (40,'2022-03-19 18:34:07','004',2, 4),
        (41,'2022-01-06 07:27:04','010',1, 9),
        (42,'2022-03-10 10:30:38','010',3, 3),
        (43,'2022-03-22 04:07:53','009',3, 5),
        (44,'2022-02-07 00:27:35','004',5, 6),
        (45,'2021-09-30 04:26:58','004',2, 5),
        (46,'2022-02-13 16:10:24','006',3, 8),
        (47,'2021-12-17 08:52:51','001',1, 6),
        (48,'2022-02-18 05:34:58','002',2, 1),
        (49,'2021-12-23 23:58:23','009',1, 9)
) AS t (sale_id, sales_at, item_id, quantity, user_id)
```

Athenaコンソールプレビュー結果
![SnapCrab_NoName_2022-4-7_0-44-50_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/5d6fb191-013c-eefb-db05-e3a5019c26f5.png)




# 論理演算子

## AND

ANDは「かつ」を意味していて、aとbの条件にANDを付けて a AND b とすると、**aかつbを満たす**という意味になります。

【例】practice_productテーブルから、unit_priceが100以上**かつ**product_groupが0のデータを出したい

```sql
SELECT
	*
FROM
	practice_product
WHERE
	unit_price >= 100
AND
	product_group = 0
```

結果
![SnapCrab_NoName_2022-4-7_0-48-34_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/c176f3a4-ec31-2fd8-25a4-10bbf926a690.png)

unit_priceが100以上でproduct_groupが0の4商品が出てきました。

## OR

ORは「または」を意味していて、aとbの条件にORを付けて a OR b とすると、**aまたはbを満たす**という意味になります。

【例】practice_productテーブルから、unit_priceが100以上**または**product_groupが0のデータを出したい

```sql
SELECT
	*
FROM
	practice_product
WHERE
	unit_price >= 100
OR
	product_group = 0
```

結果
![SnapCrab_NoName_2022-4-7_0-50-17_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/76b4ee67-a694-e054-f79f-49f21290fb65.png)


unit_priceが100以上またはproduct_groupが0の全ての商品が出てきました。
今回の例で出てこない場合があるのは、**unit_priceが100未満のproduct_groupが1の商品**となります。（存在しないのですべての商品が出てきました）

## NOT

NOTは「ではない」を意味していて、aの条件にNOTをつけて not a とすると、**aではない**という意味になります。

【例】practice_productテーブルから、unit_priceが100以上**ではない**データを出したい

```sql
SELECT
	*
FROM
	practice_product
WHERE
	NOT unit_price >= 100
```

結果
![SnapCrab_NoName_2022-4-7_0-55-4_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/0e341452-844e-0c4d-9569-eb772a7b3093.png)




unit_priceが100以上ではない2商品が出てきました。

# 比較演算子/範囲演算子

[論理演算子](#論理演算子)にて、unit_priceが100以上を`>=100`としましたが、この他、以下のような比較演算子があります

| コマンド| 内容|
| ---|--- |
|< | 未満|
|> |より大きい |
|<= | 以下|
|>= | 以上|
| =| 等しい|
| <>|等しくない |
|!=| 等しくない|
|BETWEEN a AND b | aとbの間|


こちらについては、比較のコマンドを変更して結果を試してみてください。

## BETWEEN

BETWEENのみ説明します。

`BETWEEN a AND b`は
`a <= 対象列 <= b`と同等です。

【例】practice_productテーブルから、unit_priceが200以上600以下のデータを出したい

```sql
SELECT
	*
FROM
	practice_product
WHERE
	unit_price BETWEEN 200 AND 600
```

結果
![SnapCrab_NoName_2022-4-7_1-12-54_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/7d247894-1a0f-a319-86cb-eff26e4c9ebe.png)







# データの整列

データを昇順または降順で並べたい場合、`ORDER BY 列名 ASC or DESC`  と書きます

ORDER BYはWHERE句の後、つまり一番最後に書きます。
ASCは昇順、DESCは降順です。昇順の場合、**ASCは除いても構わない**です。

## 昇順

【例】practice_productテーブルを、unit_price昇順に並べたい

```sql
SELECT
	*
FROM
	practice_product
ORDER BY unit_price ASC
```

結果

![SnapCrab_NoName_2022-4-7_1-20-58_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/48837e7f-9dde-efbe-0c20-aca0021eb8e8.png)


## 降順

【例】practice_productテーブルを、unit_price昇順に並べたい

```sql
SELECT
	*
FROM
	practice_product
ORDER BY unit_price DESC
```

結果
![SnapCrab_NoName_2022-4-7_1-22-0_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/2693a55e-e486-c803-eaaf-05edf879139e.png)



## 昇順・降順複合

ORDER BYは複数列選択することができ、それぞれ昇順降順を選択できます。

【例】practice_productテーブルを、product_groupを降順、unit_price昇順に並べたい

```sql
SELECT
	*
FROM
	practice_product
ORDER BY product_group DESC, unit_price ASC
```

結果
![SnapCrab_NoName_2022-4-7_1-24-9_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/a73ab84f-4de0-0a0e-41bc-8c1f3a840920.png)


## 注意点

昇順降順ですが、データの型に気を付けないと、**意に反する結果となる事があります**。
主には文字列型(varchar)に対して`ORDER BY`すると起こります。
型については後述しますが、Athenaのテーブルのプラスボタンから見ることができます。

![SnapCrab_NoName_2022-4-7_2-15-3_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/8e53d70d-89a9-9e94-2ce7-289766ec0be8.png)


practice_userテーブルのuser_idをわざとvarcharに変換して降順にしてみます。

```sql
SELECT * FROM practice_user
ORDER BY cast(user_id as varchar) desc
```

結果

![SnapCrab_NoName_2022-4-7_2-23-3_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/ce882341-5bce-b4a8-3891-5492581dd68a.png)


9が一番大きく、10は2番目に小さい値となってしまいました。
varchar型での並べ替えの場合、1文字目のUnicodeコードポイントでの並べ替えになるので、このような形になります。

# 別名を付ける

表や列に別名を付ける場合 `AS` を使います。
次の[集計](#集計)でASを用いますので。説明します。

【例】practice_productテーブルのproduct_groupを`group_number`という列名に変更して、全列を表示したい

```sql
SELECT
	item_id,
	product_name,
	unit_price,
	product_group as group_number
FROM
	practice_product
```

結果
![SnapCrab_NoName_2022-4-7_1-35-42_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/3b740115-9c86-c479-5680-23b96b3b23d2.png)


## 注意点

別名を付ける際、分かりやすさのために日本語を付けたくなりますが、通常とは違う書き方をする必要がありますので、お勧めしません。
※ 以下のように`”`で括る必要があります。

```sql
SELECT
	item_id,
	product_name,
	unit_price,
	product_group as "製品グループ"
FROM
	practice_product
```

# 集計

SQLではデータを取り出す際に、平均を取ったり、最大値を取ったりと集計を行うことができます。

代表的な(よく使う)ものとしては以下があります。

|コマンド | コマンド|
| ---| ---|
|MAX(列名) |列の最大値を求める | 
|MIN(列名) |列の最小値を求める |
|AVG(列名) | 列の平均値を求める|
| SUM(列名)|列の合計を求める |
|COUNT(列名) |列のデータのある行数を求める |
|COUNT(DISTINCT 列名) | 列のデータの重複値を除いた行数を求める|
|APPROX_PERCENTILE(列名, 0.5) |列の中央値を求める(パーセンタイル値を用いる) |


## MAX

【例】practice_productテーブルの、unit_price最大値を求めたい

```sql
SELECT
	MAX(unit_price) as max_unit_price
FROM
	practice_product
```

結果
![SnapCrab_NoName_2022-4-7_1-40-33_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/a42fb359-ffa5-f5a6-ac57-2a2c1ade5ca3.png)


## MIN

【例】practice_productテーブルの、unit_price最小値を求めたい

```sql
SELECT
	MIN(unit_price) as min_unit_price
FROM
	practice_product
```

結果

![SnapCrab_NoName_2022-4-7_1-41-21_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/e73d99a5-ac12-ffbd-cd6b-f0363663eab1.png)


## AVG

【例】practice_productテーブルの、unit_price平均値を求めたい

```sql
SELECT
	AVG(unit_price) as avg_unit_price
FROM
	practice_product
```

結果
![SnapCrab_NoName_2022-4-7_1-44-8_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/7a8132a2-379e-bd74-ae14-9336f977027b.png)



## SUM

【例】practice_productテーブルの、unit_price合計値を求めたい

```sql
SELECT
	SUM(unit_price) as sum_unit_price
FROM
	practice_product
```

結果
![SnapCrab_NoName_2022-4-7_1-45-27_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/aa3704f5-94be-52b5-0a67-131ee80f81c2.png)



## COUNT

【例】practice_productテーブルの、product_groupの行数を求めたい

```sql
SELECT
	COUNT(product_group) as count_product_group
FROM
	practice_product
```

結果
![SnapCrab_NoName_2022-4-7_1-46-56_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/b987e269-e5d8-6b89-bac7-bbf8d46a862d.png)

選択した列のデータにNULLがある場合、その行はカウントされません。

## COUNT(DISTINCT 列名)

【例】practice_productテーブルの、product_groupのデータ重複を除いた行数を求めたい

```sql
SELECT
	COUNT(DISTINCT product_group) as distinct_count_product_group
FROM
	practice_product
```

結果
![SnapCrab_NoName_2022-4-7_1-48-25_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/58935ec6-736c-de93-c69b-1f93487ead8f.png)
選択した列のユニークなデータの個数を求めるような形です。

## APPROX_PERCENTILE(列名, 0.5)

【例】practice_productテーブルの、unit_price中央値を求めたい

```sql
SELECT
	APPROX_PERCENTILE(unit_price, 0.5) as median_unit_price
FROM
	practice_product
```

結果
![SnapCrab_NoName_2022-4-7_1-52-49_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/9a6e949b-3200-3a13-a1be-c192d3a41ba5.png)



Athenaの場合、中央値を求める関数が無いため、パーセンタイル値の50%点で代用します。
よって、総数が偶数の場合でも**真ん中に最も近い2つの値の平均を取るようなことをしません**ので、200が返ってきます。

# グループ化

[集計](#集計)を「**特定のグループごと**」に行いたい場合、`GROUP BY` を用います。
`GROUP BY`は、WHERE句などで条件を書いた後に書きます。

【例】practice_productテーブルの、product_groupごとに、unit_price平均値を求めたい

```sql
SELECT
	AVG(unit_price) as avg_unit_price
FROM
	practice_product
GROUP BY product_group
```

結果
![SnapCrab_NoName_2022-4-7_2-2-36_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/ac3dc249-f9ad-ceca-6179-f8e1e5bce04e.png)



これだと、出てきた2つのどちらがproduct_groupの0,1なのかが分からないですね。
SELECTで列にも追加してあげるのがよく使う方法です。

```sql
SELECT
	product_group,
	AVG(unit_price) as avg_unit_price
FROM
	practice_product
GROUP BY product_group
```

結果
![SnapCrab_NoName_2022-4-7_2-4-13_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/6c867641-da3b-fd4c-7e1d-7cd9bb7bcc40.png)



このようにしてSELECTで「**特定のグループごと**」の列も呼び出す場合、**呼び出した列は必ずGROUP BYに入れる必要があります**。

- 実行できない例
```sql
SELECT
	product_group,
	item_id,
	AVG(unit_price) as avg_unit_price
FROM
	practice_product
GROUP BY product_group
```

## グループ化からの条件を付ける

`GROUP BY`をした後の結果に対してWHEREのように条件を付けたい場合があります。
その場合は`HAVING`を用います。

【例】practice_productテーブルの、product_groupごとに、unit_price平均値を求めて、unit_price平均値が500以上のデータを出したい

```sql
SELECT
	product_group,
	AVG(unit_price) as avg_unit_price
FROM
	practice_product
GROUP BY product_group
HAVING AVG(unit_price) >= 500
```

結果
![SnapCrab_NoName_2022-4-7_2-10-17_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/e53a6e20-b0a3-9d52-73f1-078ba3ab2d25.png)


Athenaでは `HAVING AVG(unit_price)`の部分を**ASで変更した列名にすることはできません**。
つまり、`HAVING avg_unit_price >= 500` とすることはできません。

# 表の結合

表を選択する`FROM`では、複数の表を選ぶことができます。

[練習用データ](#練習用データ)の表同士の関係は、以下の図のようになっています。

- practice_productとpractice_salesはitem_id同士で結合することができる
![SnapCrab_NoName_2022-4-7_11-56-55_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/581e1857-dc74-20b8-285c-4afc9ac0fa06.png)



- practice_usersとpractice_salesはuser_id同士で結合することができる
![SnapCrab_NoName_2022-4-7_12-3-0_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/72afa14e-a8b2-c8d0-574d-d7e68b59f3e6.png)



表と表は、特定の列を結合してくっつけることができます。
くっつける場合は、**どの列と列が結びついているのかをWHERE句に書く必要があります**。

【例】practice_salesとpractice_userの表を選択して結合したい

```sql
SELECT
	*
FROM
	practice_sales,practice_user
WHERE
	practice_sales.user_id = practice_user.user_id
```

結果

![SnapCrab_NoName_2022-4-7_2-38-16_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/545eec09-c384-72fe-9e49-acebaed439f5.png)


practice_salesのデータとpractice_userのデータをくっつけることができました。
ただ、**列名に同じ名前が含まれていることは好ましくありません**。

practice_sales.user_idとpractice_user.user_idで結合したので、**「user_id」という列が2つ存在してしまっています**。
表を結合したときは、同じ列名のものが無いようにして、どの列を選択してきているのかきちんと書いてあげるのが良いです。

```sql
SELECT
	practice_sales.sale_id,
	practice_sales.sales_at,
	practice_sales.item_id,
	practice_user.user_id,
	practice_user.pref
FROM
	practice_sales,practice_user
WHERE
	practice_sales.user_id = practice_user.user_id
```

列の選択では、「表名.列名」のようにしていますが、「列名」だけでSELECTすると、**どちらの表にも存在する列名の場合、エラーを吐いてしまいます**。
結合時は「表名.列名」とかいてSELECTして上げるのが良いと思います。


- どちらの表にも存在する列名の為、エラーを吐いてしまう
```sql
SELECT
	user_id
FROM
	practice_sales,practice_user
WHERE
	practice_sales.user_id = practice_user.user_id
```
![SnapCrab_NoName_2022-4-7_2-46-43_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/c45d5fc6-b639-97a9-7837-531bcb54cfe3.png)



## 結合の種類

この他、表と表を結合する操作として以下があります。
下の表では分かりづらいかもしれません。図でこの後説明しますので、そちらもご覧ください。

|コマンド | 内容|
|--- | ---|
|(INNER) JOIN | 両方に存在するデータを結合|
| LEFT (OUTER) JOIN| 左側に書いた表をベースとして結合|
| RIGHT (OUTER) JOIN| 右側に書いた表をベースとして結合|
|FULL (OUTER) JOIN | 両方に存在するデータは結合し、存在しないデータはそのままくっつく|
|CROSS JOIN | 結合に選択した列名と列名の全てのパターンを生成（クロス積）|


この後に、以下の句を書いて、結合する列名を定義します。

|コマンド |内容 |
|--- | ---|
|ON |互いの表で結合する列名をそれぞれ記載 |
| USING|互いの表に共通する列名を1つ記載 |

書き方は

```sql
SELECT
	列名
FROM 表名
INNER JOIN 表名
ON 表名.列名 = 表名.列名 (もしくは USING 列名)
```

のような形です

## INNER JOIN

`JOIN`または`INNER JOIN`と書くと、両方に存在するデータを結合することができます。

【例】practice_productとpractice_salesの先頭3行の表を使って、INNER JOINする

```sql
SELECT
	*
FROM practice_product
INNER JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
ON practice_product.item_id = practice_sales.item_id
```

結果
![SnapCrab_NoName_2022-4-7_12-12-16_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/cafe2f80-ad8b-f467-4e30-850e1b3f20a6.png)



[表の結合](#表の結合)で貼った図の赤線同士が繋がっているデータのみ抽出できました。

ちなみにこれは、FROM句で2つの表を書いてWHERE句で条件を書いた

```sql
SELECT
	user_id
FROM
	practice_sales,practice_user
WHERE
	practice_sales.user_id = practice_user.user_id
```

この例も、動作としては`INNER JOIN`と同じです。

また、例では`ON`句を使っていましたが、互いに列名が同じ場合は`USING`句を使うことができます。

```sql
SELECT
	*
FROM practice_product
INNER JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
USING (item_id)
```

結果
![SnapCrab_NoName_2022-4-7_12-16-26_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/6491503e-caf2-fa72-ee99-1a5c9616981b.png)


`USING`句の場合、**結合後に同名の列が出てくるのを避ける**ことができます
`ON`句の場合と比べて、item_id列が1つ減っています。

## LEFT JOIN

LEFT JOINは左側、つまりFROM句で選択した表をベースとして、LEFT JOINで選択した表を結合します。

【例】practice_productとpractice_salesの先頭3行の表を使って、LEFT JOINする

```sql
SELECT
	*
FROM practice_product
LEFT JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
ON practice_product.item_id = practice_sales.item_id
```

結果
![SnapCrab_NoName_2022-4-7_12-19-53_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/895f1881-3535-d5cc-5df4-e09add1052f5.png)


左側をベースとして右側を結合するので、右側にデータが無いものはNULL(データなし)として結合されます。

## RIGHT JOIN

RIGHT JOINは右側、つまり RIGHT JOINで選択した表をベースとして、FROM句で選択した表を結合します。

【例】practice_productとpractice_salesの先頭3行の表を使って、RIGHT JOINする

```sql
SELECT
	*
FROM practice_product
RIGHT JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
ON practice_product.item_id = practice_sales.item_id
```

結果
![SnapCrab_NoName_2022-4-7_12-25-6_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/abeac43b-d7e0-cf5e-7f3b-9204992b9623.png)


右側のitem_idは左側にもすべて存在するので、結果としてはINNER JOINと同じになりました。
右側にしかないitem_idがもしあった場合は、左側のデータとしてはその行はNULL(データなし)として結合されます。

## FULL JOIN

FULL JOINは両側にデータがあるものはINNER JOIN、片側データが無いものについては、もう片側がNULL(データなし)となります。
結果を見たほうが理解が早いかと思います。

practice_product表も先頭5行のみにして、以下のような関係にします。



![SnapCrab_NoName_2022-4-7_12-28-32_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/ea508841-18d7-7f15-41fd-4be9c596e288.png)

これらについて、FULL JOINしてみます。

【例】practice_productの先頭5行の表とpractice_salesの先頭3行の表を使って、FULL JOINする

```sql
SELECT
	*
FROM (SELECT * FROM practice_product limit 5) as practice_product -- practice_productから先頭5行取得した表をpractice_productと定義
FULL JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
ON practice_product.item_id = practice_sales.item_id
```

結果

![SnapCrab_NoName_2022-4-7_12-43-36_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/a482f36c-3c42-adfa-2b53-3aabc8942eb4.png)


両側にデータがあるものは互いに結合され、片方にしかないものは、もう片方がNULL(データなし)として結合されます。

## CROSS JOIN

CROSS JOINは結合する表同士の全ての組み合わせを返します。

`ON`や`USING`句は使いません。

【例】practice_productの先頭5行の表とpractice_salesの先頭3行の表を使って、CROSS JOINする

```sql
SELECT
	*
FROM (SELECT * FROM practice_product limit 5) as practice_product -- practice_productから先頭5行取得した表をpractice_productと定義
CROSS JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
```

結果
![SnapCrab_NoName_2022-4-7_12-49-27_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/f5b9a196-745b-3d5a-3c6b-4861f713125b.png)

色のついた枠を見ると分かりやすいかもしれませんが、右側の表のitem_id列1つ1つに対して左側の表のitem_idの全てのパターンが結合されています。
左側5行と右側3行のCROSS JOINなので、5 x 3で15行のデータが抽出されます。