SQLのnot existsについてまとめています。
SQLのnot existsの構文
not existsは、対象データが存在するかどうかを判定する演算子です。下記は、titles（役職テーブル）の役職がStaffの社員を、社員テーブル（employees)から抽出するSQLです。
サブクエリ内でデータが存在しないレコードを抽出しています。
SELECT * FROM employees WHERE not exists ( select * from titles where employees.emp_no = titles.emp_no and titles.title = 'Staff' ); +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | 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.70 sec)
【関連記事】
▶SQL not exists サンプルコード 2テーブルの片方にしかないデータを抽出
上記はMySQLの例ですが、not existsは標準SQLなので、OracleやSQL Server、PostgreSQLなどの狩猟DBMSで同様に使用できます。
not existsは、left joinで書き換えることが可能です。サブクエリを使わない文、直感的に理解できるSQLになりました。
SELECT DISTINCT employees.emp_no,
birth_date,
first_name,
last_name,
gender,
hire_date
FROM employees
LEFT JOIN titles
ON employees.emp_no = titles.emp_no
WHERE title != 'Staff'
LIMIT 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | 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 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 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 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)
【関連記事】
▶SQLのjoinの構文 right joinのleft join置き換え、各DBMSのjoin構文
ほかにも、not existsは、not inでの書き換えも可能です。
旧バージョンでは、not inはnot existsに書き直したほうが処理速度が向上することがありますが、最近の主要DBの最新バージョンでは、オプティマイザの機能が向上して、コードを手動でチューニングしても、処理速度はあまり変わらなくなってきているようです。
参考）『NOT IN』『NOT EXISTS』の検証 | WOWブログ
not exists、left join、not inで実行計画の内容が変わらないようなら、見やすいコードを選択するのが良いでしょう。
まとめ
ポテパンダの一言メモ
- not existsは、指定条件のデータが存在するかどうかを判定するサブクエリ用演算子
- 標準SQLのため、主要DBMSでは、not existsの利用が可能。
- not existsは、left joinやnot inを書き換えても、最新DBMSでは処理速度が特に変わらないことがある。
