はじめに

今回お仕事で初めて生の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と外部キー制約がされてます。
なので、userテーブルのidに存在しない値は入りません。

要件定義

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

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

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

抽出条件の洗い出し

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

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

依頼内容は、

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

なので

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

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

抽出（SELECT）

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

抽出にはSELECTを使います。

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

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

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

SELECT name
FROM user;

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

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

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

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

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

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

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

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

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

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までを指定してあげます。

期間の指定には、BETWEENとANDを使います。

使い方は以下を参考に。

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個以上の商品を買っている

SELECT user_id
FROM user_buy_item
WHERE quantity >= 15;

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

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

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

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

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

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

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

テーブルの結合

複数のテーブルを繋げるにはJOINを使います。テーブルの結合はいくつか種類があるのですが、今回はINNER JOINを使用します。

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

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

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

/* 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は以下のようになります。


/* 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使ってないじゃん！」
という方はこちらの記事をお読みください。

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

一応、WHEREを使って書くと


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初心者に届け。