SQLのwhereでdistinctを使う方法についてまとめています。
SQLのwhereでdistinctとINを組み合わせる
distinctは重複データを除去するSQLです。where句でdistinctとIN、サブクエリを組み合わせて絞り込み条件に組み込むことが可能です。
以下は、雇用日（hire_date）が1985年2月1日以前の社員の、最新の年収（salary）を抽出する例です。
mysql> select employees.emp_no, first_name, last_name, salary from employees left join salaries on employees.emp_no = salaries.emp_no where salaries.to_date='9999-01-01' and hire_date in ( select distinct hire_date from employees where hire_date < '1985-02-01'); +--------+-------------+--------------+--------+ | emp_no | first_name | last_name | salary | +--------+-------------+--------------+--------+ | 110022 | Margareta | Markovitch | 108407 | | 110085 | Ebru | Alpin | 88443 | | 110114 | Isamu | Legleitner | 83457 | | 110183 | Shirish | Ossenbruggen | 79229 | | 110303 | Krassimir | Wegerle | 72583 | | 110511 | DeForest | Hagimont | 72903 | | 110725 | Peternela | Onuegbe | 93193 | | 111035 | Przemyslawa | Kaelbling | 95873 | | 111400 | Arie | Staelin | 103244 | | 111692 | Tonny | Butterworth | 73953 | +--------+-------------+--------------+--------+ 10 rows in set (2.79 sec)
雇用日（hire_date）の重複を除去すると、1985-01-01と1985-01-14の２つのみになります。
mysql> select hire_date from employees where hire_date < '1985-02-01'; +------------+ | hire_date | +------------+ | 1985-01-01 | | 1985-01-01 | | 1985-01-14 | | 1985-01-01 | | 1985-01-01 | | 1985-01-01 | | 1985-01-01 | | 1985-01-01 | | 1985-01-01 | | 1985-01-01 | +------------+ 10 rows in set (0.06 sec)
【関連記事】
▶SQL distinctとcountを組み合わせてデータ種類をカウントする
employeesのデータは約30万件あり、条件に指定しているhire_dateにはindexが設定されていないため、30万件のテーブルフルスキャンが発生しています。以下は、上記SQLをexplainにて表示した実行計画です。
type=ALL、key=NULLとなっている箇所が、indexを使わずテーブルフルスキャンが実行されている箇所です。
mysql> explain select employees.emp_no, first_name, last_name, salary from employees left join salaries on employees.emp_no = salaries.emp_no where salaries.to_date='9999-01-01' and hire_date in ( select distinct hire_date from employees where hire_date < '1985-02-01'); +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------------------+--------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | PRIMARY | NULL | NULL | NULL | 299433 | 100.00 | Using where | | 1 | SIMPLE | salaries | NULL | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 9 | 10.00 | Using where | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 3 | employees.employees.hire_date | 1 | 100.00 | NULL | | 2 | MATERIALIZED | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299433 | 33.33 | Using where | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------------------+--------+----------+-------------+ 4 rows in set, 1 warning (0.00 sec)
重複を除去する方法として、distinctはgroup byと比較して処理が速いのです。しかし、where句で条件として使用すると、今回のように別の箇所でボトルネックが発生するケースもあります。
【関連記事】
▶SQL distinctのサンプルコード集 group byよりも700倍速い？
まとめ
ポテパンダの一言メモ
- distinctは重複するレコードを除去するSQL
- distinctをwhere句で使うには、INとサブクエリを組み合わせる方法がある
- distinct自体は処理が速いが、別の箇所でボトルネックが発生するケースあり
