SQLのexistsとinの違いについてまとめています。
SQLのexistsとinの違い existsはtrueかfalseしか返さない
existsは、存在するかどうかだけを判定するのに対し、inは条件に当てはまるデータを全て抽出します。また、inはカラム名を指定するのに対して、existsは無指定です。
inは対象となるレコードを返しますが、existsは存在したらtrueと返すだけです。
以下はexistsを使ったSQLの例です。employees(社員）テーブルと、肩書（titles）テーブルを結合して肩書がStaffの社員を抽出します。
SELECT * FROM employees
WHERE
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 |
+--------+------------+------------+-------------+--------+------------+
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
| 10016 | 1961-05-02 | Kazuhito | Cappelletti | M | 1995-01-27 |
| 10017 | 1958-07-06 | Cristinel | Bouloucos | F | 1993-08-03 |
| 10019 | 1953-01-23 | Lillian | Haddadi | M | 1999-04-30 |
| 10034 | 1962-12-29 | Bader | Swan | M | 1988-09-21 |
| 10038 | 1960-07-20 | Huan | Lortz | M | 1989-09-20 |
| 10039 | 1959-10-01 | Alejandro | Brender | M | 1988-01-19 |
:
:
【関連記事】
▶SQL not exists サンプルコード 2テーブルの片方にしかないデータを抽出
以下は、inを使った例です。dept_emp（部署社員紐付テーブル）で社員が30,000人以上の部署を抽出しています。
select * from departments
where departments.dept_no in (
SELECT dept_no
from dept_emp
group by dept_no
having count(*) > 30000
);
+---------+-------------+
| dept_no | dept_name |
+---------+-------------+
| d005 | Development |
| d004 | Production |
| d007 | Sales |
+---------+-------------+
【関連記事】
▶SQL in句のサンプルコード集 複数カラムの指定方法とは？
existsとinは、どちらも速度が遅い
SQLの実行速度としては、existsもinもクエリの応答速度が遅いです。一件でも見つかった時点でスキャンを中止するexistsが、SQLによっては早いケースもあります。
速度を改善するには、explainでボトルネックを確認し、場合によってはクエリを結合（join）で書き換えできないかどうかを検討しましょう。
mysql> explain SELECT * FROM employees
-> WHERE
-> exists (
-> select * from titles
-> where
-> employees.emp_no = titles.emp_no
-> and titles.title = 'Staff'
-> );
+----+-------------+-----------+------------+--------+---------------+---------+---------+-------------------------+--------+----------+-------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+--------+---------------+---------+---------+-------------------------+--------+----------+-------------------------------------+
| 1 | SIMPLE | titles | NULL | index | PRIMARY | PRIMARY | 209 | NULL | 442605 | 6.76 | Using where; Using index; LooseScan |
| 1 | SIMPLE | employees | NULL | eq_ref | PRIMARY | PRIMARY | 4 | employees.titles.emp_no | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+--------+---------------+---------+---------+-------------------------+--------+----------+-------------------------------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> explain select * from departments
-> where departments.dept_no in (
-> SELECT dept_no
-> from dept_emp
-> group by dept_no
-> having count(*) > 30000
-> );
+----+-------------+-------------+------------+-------+-----------------+-----------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+-----------------+-----------+---------+------+--------+----------+--------------------------+
| 1 | PRIMARY | departments | NULL | index | NULL | dept_name | 162 | NULL | 9 | 100.00 | Using where; Using index |
| 2 | SUBQUERY | dept_emp | NULL | index | PRIMARY,dept_no | dept_no | 16 | NULL | 331143 | 100.00 | Using index |
+----+-------------+-------------+------------+-------+-----------------+-----------+---------+------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
上記の場合、existsを使ったSQLは、possible_keysが2テーブルともPRIMARYとなっていて、インデックスが利用できていることがわかります。
また、後者のinを使ったSQLでは、departmentsに対してpossible_keysがNULLとなっており、テーブルフルスキャンが発生していますが、テーブルのデータ件数が9件のため大した速度低下が起こっていないことがわかります。
【関連記事】
▶SQLのexplainは、実行計画を表示 遅いSQLのボトルネックを発見する
まとめ
ポテパンダの一言メモ
- existsとinの違いは返す値。inは対象レコードを返すが、existsはtrueかfalseのみ返す。
- existsもinも、クエリ速度は遅いため、速度改善には結合（join)での書き換えを検討する
- ボトルネックになる箇所を発見するには、explain（実行計画）を確認する
