SQL not in データの除外指定　NULLや複数カラムを扱う方法

データの除外条件に使える、not inについてまとめました。

以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

not inの使い方

指定した固定値のリストを除外するには、以下のようにします。

SELECT * 
FROM   `departments` 
WHERE  dept_name NOT IN ( 'Sales', 'Development' )

上記のSQLは、部署名（dept_name）が「Sales」「Development」以外を部署テーブル（departments）から取得します。

実行結果はこうなります。

SQL実行結果

サブクエリとnot inの組み合わせサンプル

固定値リストではなく、動的な除外リストにしたい場合は、サブクエリ（副問い合わせ）を組み合わせます。

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)

上記SQLは、人数50,000人以上の部署を除く部署名を取得するSQLです。

少々複雑なので、サブクエリの部分のみ実行してみましょう。dept_empは部署名と社員情報の紐付けテーブルです。

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

サブクエリ部分のみ実行すると以下の結果になります。

SQL実行結果

SQL全体を実行すると、以下の結果となります。

SQLの実行結果

社員数50,000人以上の部署「Development」「Production」「Sales」以外の部署名が取得できました。

not inで「NULL以外」を指定したい場合は、IS NOT NULLを使う

not inのリストにNULLを含めると、何も取得できなくなります。

SELECT * 
FROM   `departments` 
WHERE  dept_name NOT IN ( 'Sales', 'Development', NULL )

NULLを除外する場合は、以下のようにIS NOT NULLを指定します。

SELECT * 
FROM   `departments` 
WHERE  dept_name NOT IN ( 'Sales', 'Development' ) 
       AND dept_name IS NOT NULL

not in でワイルドカードを使いたい場合は、not likeを使う

not inではワイルドカードを扱うことができません。例えば、「Sales」を除外するために以下のSQLを実行すると、意図に反して「Sales」が取得出来てしまいます。

ワイルドカードではなく「Sa%」という文字列を除外する動きになっているようです。

SELECT * 
FROM   `departments` 
WHERE  dept_name NOT IN ( 'Sa%' )

SQLの実行結果

除外リストにワイルドカードを使いたい場合は、not likeを使用します。

SELECT * 
FROM   `departments` 
WHERE  dept_name NOT LIKE ( 'Sa%' )

where句で条件をandで記述すれば、not inとnot likeを同時に使用することも可能です。

SELECT * 
FROM   `departments` 
WHERE  dept_name NOT LIKE ( 'Sa%' ) 
       AND dept_name NOT IN ( 'Development', 'Research' )

複数カラムに対してnot inを使う方法

カラム単体ではなく、複数のカラムの組み合わせに対してnot inを使うことも可能です。

SELECT * 
FROM   `departments` 
WHERE  ( dept_no, dept_name) NOT IN ( ('d002','Finance'), 
                                          ('d001','Sales') )

実行結果は以下のとおりです。

「d002 Finance」は除外されていますが、「d007 Sales」は除外されていません。not inに指定したリストはdept_no=「d001」　dept_name=「Sales」だからです。

SQL 実行結果

not inはnot existsに書き換えても高速化できないケースもあり

not inを使ったSQLをnot existsを使ったSQLに書き換え、実行速度を比較してみます。

まずは、not inを使ったSQLから実行してみましょう。

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)

実行結果は以下の通り。

• 7 total, Query took 0.0872 seconds.

SQL実行結果

続いて、not existsを使ったSQLを実行します。

SELECT *
FROM   departments d1
WHERE  NOT exists (SELECT dept_name 
                         FROM   departments d2
                                INNER JOIN dept_emp 
                                        ON dept_emp.dept_no = 
                                           d2.dept_no
                   		where d1.dept_name = d2.dept_name
                         GROUP  BY dept_name 
                         HAVING Count(emp_no) > 50000)

• 7 total, Query took 0.0871 seconds.

出力結果は同じでした。このケースでは、not inでも、not existsでも実行時間はほぼ同じとなりました。

原因として考えられるのは、データベースのオプティマイザ（最適化エンジン）です。

このケースでは、内部で実行される処理は、ほぼ同じSQLに最適化されているようです。

ただし、DBMSや古いバージョンではnot existsがパフォーマンス改善になるケースもあります。

not inのまとめ

ポテパンダの一言メモ

• not inは、固定値リストまたはサブクエリで、指定データを除外できる
• NULLを除外したい場合は、where句にIS NOT NULLを追加する
• ワイルドカードを使いたい場合は、not likeを組み合わせる
• not inは複数のカラムに対して使用可能
• not inをnot existsに置き換えても高速化できないケースもあり