T-SQL の EXCEPT 演算子

前回「T-SQL の INTERSECT 演算子」では、INTERSECT 演算子を使って、2 つの結果セットのどちらにも存在する値のみを取得しました。

今回は EXCEPT 演算子を使ってみましょう。

EXCEPT 演算子

EXCEPT は、EXCEPT の左側の結果セットに存在していて、右側の結果セットには存在していない値のみを返してくれるオペレータで、構文は次の通りです。

[ クエリー 1 ] EXCEPT [ クエリー 2 ]

構文や注意点等 INTERSECT とほぼ同じです。

まず、EXCEPT 演算子の結果セットは DISTINCT な値を返します。

それぞれのクエリーは、カラムの数、順番が一致していなければなりません。

また、データ型も一致しているか、暗黙に同じ型に変換可能なものでなければなりません。

こちらも INTERSECT と同様に、結果セットのカラム名は一番左で指定したクエリーのカラム名が使われます。

結果セットの並び順を指定したい時には ORDER BY 句を最後のクエリーに指定します。その際も、一番左で指定したカラム名を使います。

それでは、EXCEPT を使ってみましょう。

次のような #Users テーブルと #Students テーブルがあるとします。

CREATE TABLE #Users (
	UserID		INT,
	FirstName	NVARCHAR(50),
	LastName	NVARCHAR(50)
);

INSERT INTO #Users 
	(UserID, FirstName, LastName)
VALUES
	(1, 'Yukari', 'Naito'),
	(2, 'Hiroki', 'Takagi'),
	(3, 'Takahiro', 'Sato'),
	(4, 'Nao', 'Tabata'),
	(5, 'Hanako', 'Tanaka');

-----------------------------------------

CREATE TABLE #Students (
	StudentID	INT,
	FirstName	NVARCHAR(50),
	LastName	NVARCHAR(50)
);

INSERT INTO #Students 
	(StudentID, FirstName, LastName)
VALUES
	(1, 'Taro', 'Yamada'),
	(2, 'Hanako', 'Tanaka'),
	(3, 'Yuko', 'Suzuki'),
	(4, 'Takao', 'Sato'),
	(5, 'Hiroki', 'Takagi'),
	(6, 'Yuka', 'Kimura');

FirstName と LastName を取得する SELECT 文を、 EXCEPT を使って結合し、LastName 順にならべると次のようになります。

SELECT	FirstName,
	LastName
FROM	#Users
EXCEPT
SELECT	FirstName,
	LastName
FROM	#Students
ORDER BY LastName;

[ 実行結果 ]

#Users テーブルに存在して、#Students テーブルに存在しない FirstName と LastName のレコードが取得できました。

ちなみに同じようなデータを LEFT JOIN を使って次のように取得することができます。

SELECT	U.FirstName,
	U.LastName
FROM	#Users AS U
	   LEFT JOIN #Students AS S
	      ON U.FirstName = S.FirstName
	         AND U.LastName = S.LastName
WHERE	S.FirstName IS NULL
ORDER BY U.LastName;

[ 実行結果 ]

ただし、この場合は、結果セットの値は DISTINCT ではないので、左側のテーブルに同姓同名の人が二人いれば結果セットに同じ FirstName と LastName のレコードが 2 行含まれるようになります。

UNION, INTERSECT, EXCEPT 演算子の優先順位

UNION, INTERSECT, EXCEPT を使って複数の結果セットを結合した場合には、以下のような優先順位で評価されます。

括弧
INTERSECT
UNION と EXCEPT （左側が優先）

括弧がない場合は、まず INTERSECT、そして UNION と EXCEPT　は同等で左側から順番に優先して評価されます。

ただ、少し試していただくとわかるかもしれませんが、括弧を使わないと、どんな結果セットが返ってくるのかとてもわかりにくいです。

その時はわかっていても、しばらく経ってから見直したり、他の人が見たりした時にもわかりにくいと思いますので、できるだけ括弧を使ったほうがよいと思います。