MINUS、EXCEPT演算子


ITコーディネータのシュウです。

玉敷_藤の花

ゴールデンウィーク(GW)も過ぎちゃいましたね。今年は、カレンダー通り休みがとれたのですが、今の時期、どことなく憂鬱に感じるのは私だけでしょうか?

今年は、GWの期間に、埼玉県の加須市騎西(きさい)にある、玉敷(たましき)神社の藤の花を家族で見に行って来ました。この藤の木は、県指定天然記念物であり、なんと樹齢400年、幹回り4.8メートル、枝張り約700平方メートルにも及ぶそうです。すごいですね。4月下旬くらいから5月上旬くらいまでが見ごろだとか。一応、一番見ごろな時に、見れたわけです。

ところで、出店の大判焼きもうまかったですよ。家族みんなで食べるからうまいのかな?
さて、頭を切り替え、仕事、仕事。頑張るぞ~!トホホホ・・・

<本日の題材>
MINUS、EXCEPT演算子

今回は、集合演算の差を行う演算子としての、MINUS、EXCEPTについて見てみます。同じ構造の2つのテーブルA,Bがあったとして、2つのテーブルのデータが完全に一致しているのかを確認したくなるときがありますね。そのときに、一致していないデータを抽出したいときに使用できます。元々Oracleを使用していた方はMINUS 演算子がすぐに連想されるかと思いますが、標準SQLではEXCEPT(SQL-92から)が採用されているんですね。ですので、SQLServerではEXCEPT 演算子を使用します(SQL Server2005からサポート)。

例)データベースがOracleの場合:

項目数、及びデータ型が一致する2つのテーブルtbl_A、tbl_Bの定義がともに以下のようであるとします。

テーブル定義:tbl_A
  CD_A         INT
     Name_A   VARCHAR2(20)

 テーブル定義:tbl_B
 CD_B          INT
    Name_B   VARCHAR2(20)

データは、
SELECT * FROM tbl_A;
select_tblA

SELECT * FROM tbl_B;
select_tblB

テーブルtbl_A, tbl_B ともに7件のレコードがありますが、tbl_Aに存在して、tbl_Bに存在しないレコードを抽出したいときには、以下のようにします。

SELECT * FROM tbl_A
MINUS
SELECT * FROM tbl_B;

ora_minus

(どちらのテーブルにも存在し、全ての項目の値が一致しているレコードは、抽出結果に表示されません。1つの項目でも値が違っていれば、抽出されてきます。)

反対に、tbl_Bに存在して、tbl_Aに存在しないレコードを抽出したいときには、以下のようになります。

SELECT * FROM tbl_B
MINUS
SELECT * FROM tbl_A;

ora_minus2

なお、どうしてもソートを行いたい場合には、最初のSELECTの項目に別名を設定して、それをORDER BY で使用することでできます。

SELECT CD_A AS CD, Name_A AS NAME
FROM tbl_A
MINUS
SELECT CD_B, Name_B
FROM tbl_B
ORDER BY CD;

ora_minus_orderby

※MINUS演算子の後のほうのSELECTの項目に別名をつけて ORDER BY で指定した場合はエラーになります。

ora_minus_orderby_err

 なお、MINUS 演算子前後のSELECTの抽出項目の数が違う場合や、項目の定義が異なる状態でも、抽出はエラーになります。

また、今回は tbl_A と tbl_B が全く同じ構造のテーブルでテストしましたが、そうではない場合でも、データを比較したい項目を MINUS演算子の前後のSELECT 文で指定し、その項目数と型が一致していれば、同様に抽出を行うことは可能です。

例2)SQL Serverの場合:

上記の例と同様のテーブルを作成しておきます。
・tbl_Aに存在して、tbl_Bに存在しないレコードを抽出したいとき

SELECT * FROM tbl_A
EXCEPT
SELECT * FROM tbl_B;

sqlsv_except1

・反対にtbl_Bに存在して、tbl_Aに存在しないレコードを抽出したいとき

SELECT * FROM tbl_B
EXCEPT
SELECT * FROM tbl_A;

sqlsv_except2

 ・tbl_Aに存在して、tbl_Bに存在しないレコードを抽出し、CDの内容でソートしたいとき

SELECT CD_A AS CD, Name_A AS NAME
FROM tbl_A
EXCEPT
SELECT CD_B, Name_B
FROM tbl_B
ORDER BY CD;

sqlsv_except_orderby

 ※EXCEPT演算子の後のほうのSELECT 項目に別名をつけてORDER BYで指定した場合はエラーになります。

sqlsv_except_orderby_err

 ちなみに、MySQLではEXCEPT関数はサポートしていないようです。
この場合、NOT EXISTSを使用して、各項目がすべて一致するもの以外を抽出するかたちになります。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>