Help us understand the problem. What is going on with this article?

MySQLで集合差を出す

More than 3 years have passed since last update.

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するのが基本ですね

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away