【SQL】複雑な条件を指定ができるサブクエリの書き方～select where～

where句で利用するサブクエリの使い方について紹介していきます。

サブクエリとは
前提条件
サブクエリの書き方

サブクエリとは

複雑な条件の指定ができる
ある問い合わせに対してそれと異なる問い合わせを行う
select,where,from,havingなどで利用することができる

主な利用用途

売り上げの平均より高いレコードを取得できる
指定の期間内に来店していない顧客を一覧化できる

前提条件

ユーザー管理をしているUSERSテーブルと顧客の販売履歴を管理しているordersテーブルを利用

使用するテーブル：usersテーブルとordersテーブル

usersテーブル

+----+----------+------+------------------+--------+---------------+
| id | last_name| age  | email            | gender | prefecture_id |
+----+----------+------+------------------+--------+---------------+
|  1 | 柴犬     |    7 | shibainu@dog.com |      1 |            13 |
|  2 | コーギー |    8 | corgi@dog.com    |      1 |            42 |
|  3 | ダックス |   10 | daks@dog.com     |      2 |            11 |
|  4 | パグ     |    3 | pug@dog.com      |      2 |            10 |
|  5 | シーズー |   17 | shi-tzu@dog.com  |      2 |            11 |
|  6 | 秋田犬   |   12 | akitainu@dog.com |      1 |             5 |
+----+----------+------+------------------+--------+---------------+
6 rows in set (0.000 sec)

ordersテーブル

+------+---------+--------+---------------------+
| id   | user_id | amount | order_time          |
+------+---------+--------+---------------------+
|    1 |       2 |   6000 | 2030-12-31 11:00:00 |
|    2 |       3 |   2000 | 2030-12-25 18:30:00 |
|    3 |       1 |   3000 | 2030-12-10 10:00:00 |
|    4 |       4 |   2500 | 2030-11-01 20:15:00 |
|    5 |       3 |   4000 | 2030-11-30 09:45:00 |
|    6 |       5 |   3600 | 2030-10-15 23:30:00 |
+------+---------+--------+---------------------+
6 rows in set (0.008 sec)

条件

2030年12月に商品を購入していないユーザーを取得する
購入者はordersテーブルのuser_id、購入日時はorder_timeで判別
取得するカラムはid,last_name

期待値

ordersテーブルを確認すると2030年12月に購入履歴のないユーザーは№1~3の以外となる
そのことから出力されるユーザーは『パグさん、シーズーさん、秋田犬さん』

サブクエリの書き方

構文

select カラム名 from テーブル名 
where カラム名 演算子 (
  select カラム名 from テーブル名 where 条件
);

コードの見方

青文字・・・省略できない決まり文句
紫文字・・・コメント
白文字・・・利用者の任意で入力する文字列

使用するSQL文

select id,last_name from users //①
where id not in(　//②
  select user_id from orders where extract(year_month from order_time)=203012　//③
);

解説

① select id,last_name from users
取得したい情報はusersテーブルのid,last_nameカラム

② where id not in
条件に一致しないユーザーidを取得する演算子

③ select user_id from orders where extract(year_month from order_time)=203012
ordersテーブルのorder_time(購入日時)が2030年12月のユーザーidを検索

日付の指定にextract関数を使いましたが、下記のような方法で指定しても良い

order_time >='2030-12-01 00:00:00' and  order_time < '2031-01-01 00:00:00'

extract関数が良くわからない場合は以下を参照

【SQL】日付から任意の年月日を取得するextract関数の使い方

日付から任意の年月日を取得するextract関数の使い方について紹介していきます。前提条件注文履歴を管理しているテー...

出力結果

+----+-----------+
| id | last_name |
+----+-----------+
|  4 | パグ      |
|  5 | シーズー  |
|  6 | 秋田犬    |
+----+-----------+
3 rows in set (0.014 sec)

2030年12月に購入履歴のないユーザーが期待値通りに取得できました。