LoginSignup
123

More than 5 years have passed since last update.

MySQLで集合差を出す

Last updated at Posted 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
)

NOT EXISTS + 相関サブクエリ

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

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

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

LEFT JOIN + IS NULL

コメント欄で指摘いただいた方法。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するのが基本ですね

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
123