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を使うことで、条件が直感的にわかりにくくなり、バグにつながるケースもある。
- サブクエリを利用する場合は、クエリが遅くなるケースがあるので注意
