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の同時使用の場合は予期しない結果になるケースがある