SQLのexceptについて、サンプルSQLを交えてまとめています。
exceptは、差分集合を取得するSQLコード
exceptはMicrosoft SQL ServerやPostgreSQLで使える差分集合を取得するSQLです。
以下のSQLは、最初のselectで０～１０を取得し、２つ目のselectで取得した１を取り除いたものを表示します。orer by指定をしないと、順序は不定になります。
postgres=# select generate_series ( 0,10 ) except select 1;
generate_series
-----------------
6
4
5
8
2
9
0
3
7
10
(10 rows)
実際にテーブルのデータを使って差分集合を取り出してみましょう。Postgresqlのサンプルデータベースdvdrentalを使用しています。
以下のSQLは、categoryテーブル全件取得から、name（カテゴリ名）が「Drama」「Sci-Fi」を除いたものを取得します。
dvdrental=# select name from category
except
select name from category where name in ('Drama','Sci-Fi');
name
-------------
Comedy
Horror
Classics
Foreign
Games
New
Travel
Action
Music
Documentary
Sports
Animation
Children
Family
(14 rows)
さらに複雑なSQLを試してみましょう。以下のSQLは、film（映画）テーブル、film_category（映画・カテゴリ紐付けテーブル）、category（カテゴリ）テーブルから、レーティングがGまたはPG-13の映画を取得し、exceptでカテゴリがDramaまたはSciFiのものを除去しています。
dvdrental-# select title, category.name, rating, description from film
left join film_category on film.film_id = film_category.film_id
left join category on film_category.category_id = category.category_id
where rating = 'G' or rating ='PG-13'
except
select title, category.name, rating, description from film
left join film_category on film.film_id = film_category.film_id
left join category on film_category.category_id = category.category_id
where category.name in ('Drama','Sci-Fi');
title | name | rating | description
-----------------------------+-------------+--------+-----------------------------------------------------------------------------------
-------------------------------------------------
Feathers Metal | Games | PG-13 | A Thoughtful Yarn of a Monkey And a Teacher who must Find a Dog in Australia
Grease Youth | Family | G | A Emotional Panorama of a Secret Agent And a Waitress who must Escape a Composer i
n Soviet Georgia
Spinal Rocky | Family | PG-13 | A Lacklusture Epistle of a Sumo Wrestler And a Squirrel who must Defeat a Explorer
in California
Bonnie Holocaust | Documentary | G | A Fast-Paced Story of a Crocodile And a Robot who must Find a Moose in Ancient Jap
an
Happiness United | Foreign | G | A Action-Packed Panorama of a Husband And a Feminist who must Meet a Forensic Psyc
hologist in Ancient Japan
Leathernecks Dwarfs | Travel | PG-13 | A Fateful Reflection of a Dog And a Mad Cow who must Outrace a Teacher in An Aband
oned Mine Shaft
Valley Packer | Comedy | G | A Astounding Documentary of a Astronaut And a Boy who must Outrace a Sumo Wrestler
in Berlin
:
:
exceptは、標準SQLだが、一部のDBでサポートされていない
exceptは、SQL92で定義された標準SQLです。
【関連記事】
▶SQLとはどういうもの？ 独自拡張と標準SQLの大きな違いって、何？
しかし、一部のデータベースではサポートされていません。Oracleでは、exceptの代わりにminusが用意されています。
以下のSQLは、１、２，３の集合をunionで作ったあと、２をminusで除去しています。
select 1 from dual union select 2 from dual union select 3 from dual minus select 2 from dual; 1 - 1 3
また、MySQLでは、exceptの機能自体が用意されていません。not inを代替として使うことで差分集合が実現できます。
【関連記事】
▶SQL not in データの除外指定 NULLや複数カラムを扱う方法
まとめ
ポテパンダの一言メモ
- exceptは、差分集合をつくるSQL
- exceptはSQL９２で定義された標準SQLだが、一部のDBではサポートされていない。
- Oracleではminus、MySQLではnot inを代替として使う
