受講料が最大70%OFF 受講料が最大70%OFF

データの除外条件に使える、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)

実行結果は以下の通り。

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)

出力結果は同じでした。このケースでは、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に置き換えても高速化できないケースもあり

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。