SQLのnot inについてまとめています。
SQLのnot inの構文
SQLのnot inは、複数の値のいずれにも等しくないことを判定します。論理否定演算子notと演算子inを組み合わせたものです。
以下は、部署（departments）テーブルから、部署名（dept_name）が、Finance、Marketing、Quality Managementのいずれでもないレコードを抽出する例です。
SELECT * FROM departments WHERE dept_name NOT IN ( 'Finance', 'Marketing', 'Quality Management' ); +---------+------------------+ | dept_no | dept_name | +---------+------------------+ | d009 | Customer Service | | d005 | Development | | d003 | Human Resources | | d004 | Production | | d008 | Research | | d007 | Sales | +---------+------------------+ 6 rows in set (0.00 sec)
not inはサブクエリとの組み合わせでよく使われます。以下は、社員数が5万人以上の部署を除いて、departments（部署）テーブルから、データを抽出する例です。
SELECT *
FROM departments
WHERE dept_name NOT IN (SELECT dept_name
FROM departments
INNER JOIN dept_emp
ON dept_emp.dept_no =
departments.dept_no
GROUP BY dept_name
HAVING Count(emp_no) > 50000);
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
| d006 | Quality Management |
| d008 | Research |
+---------+--------------------+
6 rows in set (1.08 sec)
【関連記事】
▶SQL not in データの除外指定 NULLや複数カラムを扱う方法
2カラム以上の組に対するand／or条件をnot inで書き換える
2カラム以上をセットにした複雑なand／or条件をnot inでスッキリ記述することが可能です。
下記は、first_name（姓名の名）とlast_name（姓名の姓）が「Georgi Facello」と「Kyoichi Maliniak」を除外してemployees（社員）テーブルからデータを抽出するSQLです。
SELECT *
FROM employees
WHERE NOT ( ( first_name = 'Georgi'
AND last_name = 'Facello' )
OR ( first_name = 'Kyoichi'
AND last_name = 'Maliniak' ) )
LIMIT 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 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 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
| 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
| 10012 | 1960-10-04 | Patricio | Bridgland | M | 1992-12-18 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)
not inを使って、以下のように書き換えが可能です。直感的にわかりやすく、可読性があがりました。
mysql> select * from employees
where (first_name, last_name) not in (('Georgi','Facello'), ('Kyoichi','Maliniak'));
以下は、無条件でemployeesを10件抽出した例です。上記の結果と比較すると、社員番号（emp_no）の10001と10005が除外されているのが確認できます。
mysql> select * from employees limit 10; +--------+------------+------------+-----------+--------+------------+ | 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 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.00 sec)
【関連記事】
SQLでandとorを使った条件をinで置き換えるサンプルコード
まとめ
ポテパンダの一言メモ
- not in は、複数の値セットに含まれていないことを判定する演算子
- not inは、サブクエリとの組み合わせで使われることが多い
- 2カラム以上の値に対するand/or条件をnot inで書き換えて、可読性を上げられるケースあり
