SQLのwhere句でexistsを使ってサブクエリに合致するレコードを抽出

SQLのwhere句でexistsを使う方法についてまとめています。

SQLのwhere句でexistsをサブクエリと組み合わせて使う方法

existsは条件に合致したレコードが存在する場合に真となります。2つのテーブルを参照して、条件に合致したレコードを抽出する場合などに便利です。

以下は、employees（社員テーブル）と、titles（役職テーブル）を参照し、役職がシニアエンジニア（Senior Engneer)のレコードを抽出するSQLです。where句にexistsとサブクエリを組み合わせて条件を指定しています。

mysql> select * from employees
where exists
 ( select * from titles where titles.emp_no = employees.emp_no and title='Senior Engineer' and to_date='9999-01-01' ) 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 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10012 | 1960-10-04 | Patricio   | Bridgland | M      | 1992-12-18 |
|  10018 | 1954-06-19 | Kazuhide   | Peha      | F      | 1987-04-03 |
|  10026 | 1953-04-03 | Yongqiao   | Berztiss  | M      | 1995-03-20 |
|  10027 | 1962-07-10 | Divier     | Reistad   | F      | 1989-07-07 |
|  10029 | 1956-12-13 | Otmar      | Herbst    | M      | 1985-11-20 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.22 sec)

【関連記事】
▶【使いこなせる？】SQLのEXISTS文の使い方を初心者向けに解説 

existsはinを使って書き換えが可能です。

select * from employees
where emp_no in 
 ( select emp_no as emp_no from titles where titles.emp_no = employees.emp_no and title='Senior Engineer' and to_date='9999-01-01' ) limit 10;

existsは存在するかどうか、inはマッチするレコードをすべて抽出するためexistsのほうがレスポンスが速いとされています。しかし、最新のDBMSでは最適化によって大きな速度差が出ないケースもあります。

自己結合（相関サブクエリ）でexistsを使う

以下のように、自己結合のクエリとexistsを組み合わせることも可能です。自己結合とは、単一のテーブル自身で結合をおこなうクエリです。

下記の例では、employees（社員テーブル）から、birth_date（誕生日）が同一の社員が存在するレコードを抽出しています。

mysql> select * from employees x
where exists 
 (select * from employees y where x.birth_date=y.birth_date) limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  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 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (18.94 sec)

メインクエリでテーブルにxと別名をつけ、サブクエリ内ではyと別名をつけて結合をおこなっているんですね。今回のデータでは、emp_noが10001～10010すべての社員に、誕生日が同一の社員が存在するという結果になりました。

例えば、emp_no=10001の誕生日1953年9月2日と誕生日が同一の社員は63人存在します。

mysql> select count(*) from employees where birth_date='1953-09-02';
+----------+
| count(*) |
+----------+
|       63 |
+----------+
1 row in set (0.07 sec)

まとめ

ポテパンダの一言メモ

• existsは、where句で指定した条件が存在するかどうかを評価する演算子
• existsはinで書き換えが可能
• existsは自己結合（相関サブクエリ）でも使用可能