More than 5 years have passed since last update.

MySQLで集合差を出す

Last updated at 2016-02-16Posted at 2016-02-07

MySQL方言にはMINUSとかEXCEPTが無いので、集合の差分を表現するのが意外と面倒くさい。大抵の場合、相関サブクエリでNOT EXISTSを使ったSQLを書くことになる。

一般従業員を示すemployeeとリーダー職を示すleaderという二種類のテーブルがあるとする。

CREATE TABLE employee (
  id INT NOT NULL,
  PRIMARY KEY(id)
);
CREATE TABLE leader (
  id INT NOT NULL,
  PRIMARY KEY(id)
);

employeeにはleaderの中身も含まれているとする。さて問題。employeeではあるが、leaderではないidを洗い出したい。

employee集合からleader集合を引くことができれば、該当のid群を洗い出せる。

-- イメージ的にはこうしたいんよ
employee EXCEPT leader

NOT IN (非推奨)

MySQLにEXCEPTのような機能はない。そこで素直に書くならNOT INになるけれどNOT INは遅いことが多い。

SELECT employee.id
  FROM employee
 WHERE employee.id NOT IN (
   SELECT leader.id
     FROM leader
)

そこで相関サブクエリの形に修正すると多少緩和される。

SELECT employee.id
  FROM employee
 WHERE NOT EXISTS (
   SELECT leader.id
     FROM leader
    WHERE leader.id = employee.id
    LIMIT 1
)

同じように複数の集合を引きたいのなら、AND NOT EXISTS ...というように続けていけば計算できる。

コメント欄で指摘いただいた方法。indexが使える場合、NOT EXISTSよりも更に速いことが多い。

SELECT employee.id
  FROM employee
  LEFT JOIN leader
         ON leader.id = employee.id
 WHERE leader.id IS NULL

leader側もデータがないものだけがleader.id IS NULLになる。つまり集合差になる。

基本的にLEFT JOIN + IS NULL方式、indexがない場合は NOT EXISTS方式、という感じだろうか。なんにせよ実行前にexplainするのが基本ですね

124