SQLで「以外」の条件を指定する方法をまとめています。

SQLで「以外」を指定するサンプルコード

SQLで~以外を抽出するにはいくつか方法があります。NOT(否定)を組み合わせるものが多いので、コードを見て直感的に条件が読み取れないケースもあります。バグが発生しやすいので注意しましょう。

指定条件以外を指定するには「<>」か「!=」

単一の指定条件以外を指定するには、演算子に<>もしくは、!=を使用します。以下のサンプルは、departments(部署)テーブルから、dept_no(部署番号)がd009以外を抽出します。

mysql> select * from departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
9 rows in set (0.00 sec)

mysql> select * from departments where dept_no != 'd009';
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
8 rows in set (0.01 sec)

指定した条件以外を抽出するには、WHERE条件式にNOTで否定

複数の条件に合致したデータ以外を抽出するには、ANDやORで接続した条件にNOTをつけます。以下は、deprtments(部署)テーブルから、dept_noがd005、またはdept_nameがSalesのデータ以外を抽出します。言い換えると、dept_noがd005以外でかつ、dept_nameがSales以外のデータを抽出します。

mysql> select * from departments 
where NOT ( dept_no = 'd005' or dept_name = 'Sales' );
+---------+--------------------+
| 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)

【関連記事】
【SQL】論理否定を知る。NOTの作用と使い方についてカンタン解説

あいまい指定した条件以外を抽出するには、「NOT LIKE」

あいまい検索したデータ以外を抽出するには、NOT LIKEを使用します。以下のサンプルでは、ワイルドカード指定で%ma%に合致するもの以外を抽出します。「Marketing」や「Human Resources」などdept_nameにmaを含むデータが除外されています。

mysql> select * from departments 
where dept_name not like '%ma%';
+---------+------------------+
| dept_no | dept_name        |
+---------+------------------+
| d009    | Customer Service |
| d005    | Development      |
| d002    | Finance          |
| d004    | Production       |
| d008    | Research         |
| d007    | Sales            |
+---------+------------------+
6 rows in set (0.00 sec)

【関連記事】
【SQL】LIKEの否定検索「NOT LIKE句」について、コードを交えながら具体的に解説。

指定集団を除外して抽出するには、「NOT IN」

複雑な条件を指定して、それ以外を抽出するにはNOT INとサブクエリを組み合わせます。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);

+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d006    | Quality Management |
| d008    | Research           |
+---------+--------------------+
6 rows in set (2.15 sec)

【関連記事】
SQL not in データの除外指定 NULLや複数カラムを扱う方法 

存在しているもの以外を抽出するには、「NOT exists」

2テーブルを比較して、片方にしかデータが存在しないものだけを抽出するには、NOT existsを使用します。以下のサンプルでは、employees(社員)テーブルから、対応するtitles(肩書)テーブルを参照し、肩書が「Staff」以外のデータを抽出します。

SELECT * FROM employees 
WHERE 
	not exists (
		select * from titles 
		where 
			employees.emp_no = titles.emp_no 
			and titles.title = 'Staff'
	)
limit 10;

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | xGeorgi    | Facello   | M      | 1986-06-26 |
|  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 |
|  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 |
|  10012 | 1960-10-04 | Patricio   | Bridgland | M      | 1992-12-18 |
|  10013 | 1963-06-07 | Eberhardt  | Terkki    | M      | 1985-10-20 |
|  10014 | 1956-02-12 | Berni      | Genin     | M      | 1987-03-11 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)

【関連記事】
SQL not exists サンプルコード 2テーブルの片方にしかないデータを抽出 

まとめ

ポテパンダの一言メモ
  • 「~以外」を指定するには、NOT IN、NOT LIKE、NOT existsなどがある
  • NOTを使うことで、条件が直感的にわかりにくくなり、バグにつながるケースもある。
  • サブクエリを利用する場合は、クエリが遅くなるケースがあるので注意

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

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

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

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

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

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

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

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

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

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

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