INは、特定の値があるリスト内に存在するかどうかを確認するための条件演算子です。
※複数の値と比較して、特定の条件に合致する行を抽出することが可能
IN / NOT IN
記述
WHERE IN (idなど) WHERE IN (サブクエリ)
【usersテーブル 一覧表示】
mysql> SELECT * FROM users;
+----+-------+--------+
| id | name | job_id |
+----+-------+--------+
| 1 | user1 | 1 |
| 2 | user2 | 2 |
| 3 | user3 | 1 |
| 4 | user4 | 3 |
| 5 | user5 | 2 |
+----+-------+--------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM jobs;
+----+----------------------+
| id | job |
+----+----------------------+
| 1 | programmer |
| 2 | web designer |
| 3 | security engineer |
+----+----------------------+
3 rows in set (0.00 sec)
【含む】
mysql> SELECT * FROM users WHERE id IN (1,3);
+----+-------+--------+
| id | name | job_id |
+----+-------+--------+
| 1 | user1 | 1 |
| 3 | user3 | 1 |
+----+-------+--------+
2 rows in set (0.00 sec)
【含まない】
mysql> SELECT * FROM users WHERE id NOT IN (1,3);
+----+-------+--------+
| id | name | job_id |
+----+-------+--------+
| 2 | user2 | 2 |
| 4 | user4 | 3 |
| 5 | user5 | 2 |
+----+-------+--------+
3 rows in set (0.00 sec)
【サブクエリ】
mysql> SELECT * FROM users WHERE job_id IN (
-> SELECT id FROM jobs
-> WHERE job_id = jobs.id AND jobs.job = 'web programmer'
-> );
+----+-------+--------+
| id | name | job_id |
+----+-------+--------+
| 1 | user1 | 1 |
| 3 | user3 | 1 |
+----+-------+--------+
2 rows in set (0.00 sec)