T-SQL の INTERSECT 演算子

前回「 UNION と UNION ALL で結合　」では、UNION 演算子を使って、2 つの結果セットをひとつに結合してみました。

今回は INTERSECT 演算子を使って、2 つの結果セットのどちらにも存在する値のみを取得してみましょう。

INTERSECT 演算子

INTERSECT は、指定した左右の結果セットの両方に存在する値のみを返してくれるオペレータで、構文は次の通りです。

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

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

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

結果セットのカラム名は一番左で指定したクエリーのカラム名が使われます。

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

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

次のような #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 文を INTERSECT を使って結合し、LastName 順にならべると次のようになります。

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

[ 実行結果 ]

#Users テーブルと #Students テーブルのどちらのテーブルにも FirstName と LastName が存在する二人のレコードが結果セットとして取得できました。

INTERSECT 演算子と INNER JOIN の違い

これだけ見ると #Users テーブルと #Students テーブルを以下のように INNER JOIN したのと同じように見えますが、大きく違う点があります。

SELECT	U.FirstName,
	U.LastName
FROM	#Users AS U
	   INNER JOIN #Students AS S
	      ON U.FirstName = S.FirstName
	         AND U.LastName = S.LastName
ORDER BY LastName;

[ 実行結果 ]

まず、INTERSECT は DISTINCT の値を返します。

例えば、先程の #Users テーブルに同姓同名の人を一人追加して、上の INTERSECT のクエリーを実行しても、得られる結果セットは同じになります。

一方 INNER JOIN のほうは、DISTINCT を指定しない限り、同姓同名でも別々の行として取得されます。

INSERT INTO #Users 
	(UserID, FirstName, LastName)
VALUES
	(6, 'Hiroki', 'Takagi');

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

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

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

SELECT	U.FirstName,
	U.LastName
FROM	#Users AS U
	   INNER JOIN #Students AS S
	      ON U.FirstName = S.FirstName
	         AND U.LastName = S.LastName
ORDER BY LastName;

[ 実行結果 ]

もうひとつ、大きな違いは、NULL の扱いです。

INTERSECT は NULL 同士をイコールとみなし、左右のクエリーの結果が両方 NULL の場合はレコードを返します。

一方 INNER JOIN の結合の条件では NULL = NULL は false なので、両方 NULL でマッチしていても NULL のレコードは返ってきません。

#Users テーブルと #Students テーブルの両方に NULL のレコードを挿入して、同じクエリーを実行してみましょう。

INSERT INTO #Users 
	(UserID, FirstName, LastName)
VALUES
	(7, NULL, NULL);

INSERT INTO #Students 
	(StudentID, FirstName, LastName)
VALUES
	(7, NULL, NULL);

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

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

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

SELECT	U.FirstName,
	U.LastName
FROM	#Users AS U
	   INNER JOIN #Students AS S
	      ON U.FirstName = S.FirstName
	         AND U.LastName = S.LastName
ORDER BY LastName;

[ 実行結果 ]

INTERSECT のほうは FirstName と LastName が NULL のレコードも結果セットに含まれていますね！