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

Hiraku
PHP, Go界隈をうろうろしています。最近はgRPCと戦ってる。 特に明示していなければ、記事中のソースコード片は `CC-0 1.0` とします。出典表示無しで自由にコピペして頂いて構いません。 ただ、記事自体をコピペされるのは嫌なので、ソースコード部分以外の文章は通常通り全ての著作権を私が保持するものとします。 引用を超える範囲のコピペは止めて下さい。
http://blog.tojiru.net/
mercari
フリマアプリ「メルカリ」を、グローバルで開発しています。
https://tech.mercari.com/
Why not register and get more from Qiita?
  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