SQLの「含まない」を指定する方法についてまとめています。
目次
SQLの含まない指定は、NOT IN
「含まない」条件しては、NOT INを使います。以下の例は、部署テーブル（departments）から、部署名に「Sales」と「Development」を含まない部署を取得する例です。
SELECT * FROM `departments` WHERE dept_name NOT IN ( 'Sales', 'Development' ); +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d009 | Customer Service | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d004 | Production | | d006 | Quality Management | | d008 | Research | +---------+--------------------+ 7 rows in set (0.05 sec)
除外対象が多い・条件が複雑な場合は、NOT INとサブクエリが有効
条件を単純な直値で指定できない場合、サブクエリを組み合わせることで、複雑な「含まない」指定が可能です。
以下のサンプルは、人数50,000人以上の部署を除外して表示するSQLです。サブクエリ部分では、人数50,000人以上の部署名を取得しています。
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.71 sec)
複数カラムの組み合わせに対して、含まない指定
複数のカラムに対して「含まない」条件を指定するには、以下のようにします。以下は、部署テーブル（departments）から、d002 Financeと、d001 Salesの部署を除外して部署名を取得するSQLです。
(‘d002′,’Finance’), (‘d001′,’Sales’) など、複数の値の組をカッコで閉じてカンマで区切る表現を「タプル」と呼びます。
SELECT *
FROM `departments`
WHERE ( dept_no, dept_name) NOT IN ( ('d002','Finance'),
('d001','Sales') );
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d003 | Human Resources |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
+---------+--------------------+
8 rows in set (0.00 sec)
NULLの扱いに注意
NOT INを使う上で、直感的にわかりにくいのがNULLの扱い。
部署名（dept_name）が「Sales、Development、NULL」以外のデータを取得する意図で、以下のSQLを実行しても、何も取得できません。
SELECT * FROM `departments` WHERE dept_name NOT IN ( 'Sales', 'Development', NULL ); Empty set (0.00 sec)
NULLに関する条件はタプルに加えるとうまく動かないんですね。
SELECT *
FROM `departments`
WHERE dept_name NOT IN ( 'Sales', 'Development' )
AND dept_name IS NOT NULL;
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
+---------+--------------------+
7 rows in set (0.00 sec)
NULLを除外する場合は、上記のように条件をANDで接続して、IS NOT NULLを指定します。
まとめ
ポテパンダの一言メモ
- 含まない条件の指定はNOT INを使う
- 複雑な除外条件がある場合は、NOT INをサブクエリを組み合わせる
- NOT INとNULLの同時使用の場合は予期しない結果になるケースがある
