SQLのSELECT文で複数のテーブルから複数の条件で抽出するメモ

## はじめに

今回お仕事で初めて生のSQLを書くことになった新米デベロッパーです。

備忘録として残しますが、間違っていた場合指摘していただけると幸いです。

## やりたいこと

今回は`Sequel Ace`のクエリを利用して、複数のテーブルから複数の条件でデータを抽出したいです。

例として、ECサイトでユーザーのテーブルと買った商品のテーブルを用意しました。

### テーブル

`userテーブル`
|id|name|created_at|updated_at|
|:-:|:-:|:-:|:-:|
|1|戦場ヶ原|1988-07-07 22:22:22|NULL|
|2|小鳥遊|1997-10-24 11:11:11 |NULL|
|3|孫|2000-05-09 00:00:00|NULL|

よくあるユーザーさんが登録された時の情報が入ってるテーブルです。
今回必要なモノ以外は省いているので、とても質素。

`user_buy_itemテーブル`
|id|user_id|item_id|quantity|created_at|updated_at|
|:-:| :-: | :-: | :-: | :-: | :-: |
|1|3|59|30|2000-08-08 12:34:56|NULL|
|2|1|74|4|2000-08-11 12:12:12|NULL|
|3|2|77|15|2000-09-24 01:20:55|NULL|

買い物をした時に登録されるテーブルです。
idはインクリメント、user_idはuserテーブルのidと[外部キー制約](https://www.dbonline.jp/mysql/table/index11.html)がされてます。
なので、userテーブルのidに存在しない値は入りません。

### 要件定義

「1990年以降に登録されたユーザーさんの中から、2000年8月に商品を15個以上購入された方のidを抽出してほしい」

と依頼されたとします（ポイント付与とかで使うんでしょう、きっと）。

この場合、2つのテーブルをまたいで複数の条件で抽出する必要があります。

## 抽出条件の洗い出し

まず、いきなりSQLを書き始めても出来ません（少なくとも僕は）。

なので、まずは依頼内容を見つつどのテーブルのどの列が関係してくるかを調べます。

依頼内容は、

> 1990年以降に登録されたユーザーさんの中から、2000年8月に商品を15個以上購入された方のid

なので

1. 1990年以降に登録している
1. 2000年8月に買い物をしている
1. 15個以上の商品を買っている

という3つの条件が必要ですね。

### 抽出（SELECT）

次にそれぞれの条件をで抽出するには、どのようなSQLが必要か考えます。

抽出には`SELECT`を使います。

```sql
SELECT [列名]
FROM [テーブル名]
```

で特定のテーブルから指定した列を取り出すことができます。

例えば、ただ単純にuserテーブルのnameを抜き出す時は以下のように

```sql
SELECT name
FROM user;

/* 実行結果 */
> 戦場ヶ原
> 小鳥遊
> 孫
```

こんな感じで返ってきます。

ですがこれだけでは条件式で抽出できていないので、`WHERE`を使って条件を追加しましょう。

```sql
/* idが2のユーザー名を抽出 */
SELECT name
FROM user
WHERE id = 2;

/* 実行結果 */
> 小鳥遊
```

上記のように、比較演算子を利用して条件式を加えることができます。

### それぞれをSQLで書いてみる

では、実際に書いてみましょう。

> ① 1990年以降に登録している

```sql
SELECT id
FROM user
WHERE created_at >= '1990-01-01 00:00:00';

/* 実行結果 */
> 2
> 3
```

> ② 2000年8月に買い物をしている

ここが少し曲者で、期間を2000年の8月に限定しているので、8月1日 00:00:00 〜 8月31日23:59:59までを指定してあげます。

[期間の指定](https://www.bold.ne.jp/engineer-club/sql-select)には、`BETWEEN`と`AND`を使います。

使い方は以下を参考に。

```sql
SELECT user_id
FROM user_buy_item
WHERE created_at BETWEEN '2000-08-01 00:00:00' AND '2000-08-31 23:59:59';

/* 実行結果 */
> 1
> 3
```

> ③ 15個以上の商品を買っている

```sql
SELECT user_id
FROM user_buy_item
WHERE quantity >= 15;

/* 実行結果 */
> 2
> 3
```

これでそれぞれの条件での抽出が終わりました。

全部に共通したidは3なので、「今回は3を抽出したいんだな」と思っていてください。

実際はもっとデータ数が多くなるので、このSQLを1つにまとめて書きましょう。

## 実際に全ての条件で抽出する

本題まで少し遠回りしてしまいましたが、これから今までやった2つのテーブルを跨いだ3つの条件で抽出していきます。

そのためには、2つのテーブルを繋げる必要があります。

### テーブルの結合

複数のテーブルを繋げるには`JOIN`を使います。[テーブルの結合](https://atmarkit.itmedia.co.jp/ait/spv/0101/27/news002.html)はいくつか種類があるのですが、今回は`INNER JOIN`を使用します。

またテーブル結合した場合、同じ名前の列が存在するものを指定するとエラーが出てしまうので、`.`（ドット）で繋いであげましょう。

それと`AS`を使うことで、長いテーブル名を短縮できます。

`ON`で結合条件を決めます。
共通する列をここで一括りにしちゃいましょう。

```sql
/* userテーブルのidを指定 */
SELECT user.id
FROM user
INNER JOIN user_buy_item
/* user_buy_itemは長いので短縮 */
AS ubi
/* 左右が一致するものを抽出 */
ON user.id = ubi.user_id
```

### いざ実践

ここからは要件である

> 1990年以降に登録し、2000年8月に15個以上の商品を購入したユーザーのid

を抽出します。

この記事で得た知識を総動員して、抽出していきましょう。

SQLは以下のようになります。

```sql

/* userテーブルのidを抽出 */
SELECT user.id
FROM user
/* userテーブルとuser_buy_itemテーブルを結合 */
INNER JOIN user_buy_item
/* 以下 user_buy_item は ubi とする */
AS ubi
/* 結合条件 */
ON user.id = ubi.user_id
/* 1990年以降に登録 */
AND user.created_at >= '1990-01-01 00:00:00'
AND ubi.created_at 
/* 8月に購入 */
BETWEEN '2000-08-01 00:00:00'
AND '2000-8-31 23:59:59'
/* 商品を15個以上 */
AND ubi.quantity >= 15;
```

「`WHERE`使ってないじゃん！」
という方は[こちらの記事](http://www.code-magagine.com/?p=14748)をお読みください。

ONを1つの条件式と見なすことができます。
（実際僕の環境でも動きました）

一応、`WHERE`を使って書くと

```sql

SELECT user.id
FROM user
INNER JOIN user_buy_item
AS ubi
ON user.id = ubi.user_id
WHERE user.created_at >= '1990-01-01 00:00:00'
AND ubi.created_at 
BETWEEN '2000-08-01 00:00:00'
AND '2000-8-31 23:59:59'
AND ubi.quantity >= 15;
```

こんな感じになると思います。

## 終わりに

長文・駄文でしたが読んでいただきありがとうございました。

間違った点や、「もっと簡単に書けるよ」などありましたら教えていただけると幸いです。

自分のようなSQL初心者に届け。