SQLのinの構文、右辺にNULLが入っているときの扱い、DBMSごとに異なる右辺に記述できる式の数についてまとめています。
SQL inの構文
SQLのin演算子は、複数の値の中に一致するものが存在すれば真（１）、存在しなければ（０）を返します。
mysql> select 1 in (1,2);
+------------+
| 1 in (1,2) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mysql> select 0 in (1,2);
+------------+
| 0 in (1,2) |
+------------+
| 0 |
+------------+
1 row in set (0.00 sec)
mysql> select 'a' in ('a','b');
+------------------+
| 'a' in ('a','b') |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
SQLのwhere条件にinを使う例は以下の通りです。employeesテーブルのemp_noが10001、10002、10003、10004のデータを抽出します。
mysql> select * from employees where emp_no in ( 10001, 10002, 10003, 10004 ); +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | +--------+------------+------------+-----------+--------+------------+ 4 rows in set (0.00 sec)
not inと指定すると、指定した値と一致しないものを抽出することが可能です。以下の例はdepartments（部署）テーブルのdept_name（部署名）が、Marketing、Sales、Development以外のデータを抽出します。
mysql> SELECT * FROM departments
where dept_name not in ('Marketing','Sales', 'Development');
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d002 | Finance |
| d003 | Human Resources |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
+---------+--------------------+
6 rows in set (0.04 sec)
【関連記事】
▶SQL in句のサンプルコード集 複数カラムの指定方法とは？
SQLのinはNULLが入っていると、一致しない場合にNULLが返ってくる
in演算子を使う場合は、NULLの扱いに注意です。右辺にNULLが入っていた場合、「どれにも一致しない場合」は、NULLが返ってきます。
mysql> select 1 in ( 1,2,3,NULL ); +---------------------+ | 1 in ( 1,2,3,NULL ) | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec) mysql> select 0 in ( 1,2,3,NULL ); +---------------------+ | 0 in ( 1,2,3,NULL ) | +---------------------+ | NULL | +---------------------+ 1 row in set (0.00 sec)
DBMSごとのin演算子の違い
Oracleのin演算子
以下は、OracleでINを使う例です。
SELECT * FROM employees
WHERE job_id IN
('PU_CLERK','SH_CLERK')
ORDER BY employee_id;
SELECT * FROM employees
WHERE salary IN
(SELECT salary
FROM employees
WHERE department_id =30)
ORDER BY employee_id;
なお、inの左辺にある式と右辺は同じデータ型で構成されている必要があります。式は、最大1000個の式を指定できます。
SQL Serverのin演算子
以下は、SQL ServerでINを使う例です。
-- Uses AdventureWorks
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');
GO
関連）IN (Transact-SQL) – SQL Server | Microsoft Docs
かっこで囲んだINの右辺に極端に多くの値 (コンマで区切られた数千単位の値) を指定すると、リソースが消費されてエラー 8623 または 8632 が返される場合があります。 あまりにも多くの値を指定しなければならない場合は、INリストにサブクエリを使用します。
DBMSごとのin演算子の扱いの違い
MySQLのin演算子
MySQLでINを使う例は以下の通り。
mysql> SELECT 2 IN (0,3,5,7);
-> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
-> 1
関連）MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.3.2 比較関数と演算子
MySQLのINの右辺に使える値の数は、max_allowed_packet 値によって制限されます。max_allowed_packetの値は以下のように確認可能です。
mysql> select @@max_allowed_packet; +----------------------+ | @@max_allowed_packet | +----------------------+ | 67108864 | +----------------------+ 1 row in set (0.00 sec)
PostgreSQLのin演算子
PostgreSQLのINを使う例は以下の通り。
expression = value1 OR expression = value2 OR ...
PostgreSQLの公式ドキュメントには、右辺に使える値の数に関する記述は特にありませんでした。
まとめ
- inは、複数の値に一致するものが含まれるかどうかを判定する演算子。
- inの右辺にNULLが入っている場合、一致しない場合の値はNULLになる。
- inの右辺に記述できる数はDBMSによって違いがある。
