SQL inの構文 右辺のNULLの扱いやDBMSごとの式の数制限

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;

関連）Oracle公式ドキュメント：IN条件

なお、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公式ドキュメント：副問い合わせ式

PostgreSQLの公式ドキュメントには、右辺に使える値の数に関する記述は特にありませんでした。

まとめ

ポテパンダの一言メモ

• inは、複数の値に一致するものが含まれるかどうかを判定する演算子。
• inの右辺にNULLが入っている場合、一致しない場合の値はNULLになる。
• inの右辺に記述できる数はDBMSによって違いがある。