LoginSignup
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@motoJinC25(鄭 鎮健)

Except演算子の仕様について

Posted at

Untitled.png

背景

データベース関連処理の単体テストとして比較対象(処理結果)をテーブルAに保存し、期待値をテーブルBに保存してテーブル間を比較して検証するため、仕様を整理します。

基本仕様

SQL Server の Except演算子

  • テーブルAで取得した行(レコード)とテーブルBで取得した行を全ての列(カラム)の値で比較し、列の値がすべて一致する行は抽出しません。
  • テーブルAのみにある行(レコード)を抽出します。
  • 2つのテーブルの列数が同じであることが必要です。
  • 列名は異なっていても比較できます。
  • 集合演算子で、差集合を求めます。

Untitled 1.png

仕様確認

1. 集合演算子の差集合確認

テーブルA [Employee]

DROP TABLE IF EXISTS [Employee];	
CREATE TABLE [Employee] (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (1, '鈴木', 'suzuki');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (3, '佐藤', 'sato');
Id Name Romaji
1 鈴木 suzuki
2 田中 tanaka
3 佐藤 sato

テーブルB [Employee_except]

DROP TABLE IF EXISTS [Employee_except];	
CREATE TABLE employee_except (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
Id Name Romaji
2 田中 tanaka

検証1-1

SELECT * FROM [Employee]
EXCEPT
SELECT * FROM [Employee_except]
Id Name Romaji
1 鈴木 suzuki
3 佐藤 sato

検証1-2

SELECT * FROM [Employee_except]
EXCEPT
SELECT * FROM [Employee]
Id Name Romaji

検証1-3

SELECT [Id], [Romaji], [Name] FROM [Employee]										
EXCEPT										
SELECT [Id], [Name], [Romaji] FROM [Employee_except]
Id Romaji Name
1 suzuki 鈴木
2 tanaka 田中
3 sato 佐藤

2. 差集合のヌル確認

テーブルA [Employee]

DROP TABLE IF EXISTS [Employee];	
CREATE TABLE [Employee] (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (1, '鈴木', 'suzuki');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (2, '田中', NULL);
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (3, '佐藤', 'sato');
Id Name Romaji
1 鈴木 suzuki
2 田中 NULL
3 佐藤 sato

テーブルB [Employee_except]

DROP TABLE IF EXISTS [Employee_except];	
CREATE TABLE employee_except (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
Id Name Romaji
2 田中 tanaka

検証2-1

SELECT * FROM [Employee]
EXCEPT
SELECT * FROM [Employee_except]
Id Name Romaji
1 鈴木 suzuki
2 田中 NULL
3 佐藤 sato

検証2-2

SELECT * FROM [Employee_except]
EXCEPT
SELECT * FROM [Employee]
Id Name Romaji
2 田中 tanaka

3. 両集合のヌル確認

テーブルA [Employee]

DROP TABLE IF EXISTS [Employee];	
CREATE TABLE [Employee] (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (1, '鈴木', 'suzuki');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (2, '田中', NULL);
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (3, '佐藤', 'sato');
Id Name Romaji
1 鈴木 suzuki
2 田中 NULL
3 佐藤 sato

テーブルB [Employee_except]

DROP TABLE IF EXISTS [Employee_except];	
CREATE TABLE employee_except (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (2, '田中', NULL);
Id Name Romaji
2 田中 NULL

検証3-1

SELECT * FROM [Employee]
EXCEPT
SELECT * FROM [Employee_except]
Id Name Romaji
1 鈴木 suzuki
3 佐藤 sato

検証3-2

SELECT * FROM [Employee_except]
EXCEPT
SELECT * FROM [Employee]
Id Name Romaji

4. カラム名不一致の確認

テーブルA [Employee]

DROP TABLE IF EXISTS [Employee];	
CREATE TABLE [Employee] (	
	[Id] int,
	[Name2] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee] ([Id], [Name2], [Romaji]) VALUES (1, '鈴木', 'suzuki');
INSERT INTO [Employee] ([Id], [Name2], [Romaji]) VALUES (2, '田中', 'tanaka');
INSERT INTO [Employee] ([Id], [Name2], [Romaji]) VALUES (3, '佐藤', 'sato');
Id Name2 Romaji
1 鈴木 suzuki
2 田中 tanaka
3 佐藤 sato

テーブルB [Employee_except]

DROP TABLE IF EXISTS [Employee_except];	
CREATE TABLE employee_except (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
Id Name Romaji
2 田中 tanaka

検証4-1

SELECT * FROM [Employee]
EXCEPT
SELECT * FROM [Employee_except]
Id Name2 Romaji
1 鈴木 suzuki
3 佐藤 sato

検証4-2

SELECT * FROM [Employee_except]
EXCEPT
SELECT * FROM [Employee]
Id Name Romaji

5. 重複レコードの確認

テーブルA [Employee]

DROP TABLE IF EXISTS [Employee];	
CREATE TABLE [Employee] (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (1, '鈴木', 'suzuki');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (3, '佐藤', 'sato');
Id Name Romaji
1 鈴木 suzuki
2 田中 tanaka
2 田中 tanaka
3 佐藤 sato

テーブルB [Employee_except]

DROP TABLE IF EXISTS [Employee_except];	
CREATE TABLE employee_except (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
Id Name Romaji
2 田中 tanaka

検証5-1

SELECT * FROM [Employee]
EXCEPT
SELECT * FROM [Employee_except]
Id Name Romaji
1 鈴木 suzuki
3 佐藤 sato

検証5-2

SELECT * FROM [Employee_except]
EXCEPT
SELECT * FROM [Employee]
Id Name Romaji

6. 完全一致の確認 (1/3)

テーブルA [Employee]

DROP TABLE IF EXISTS [Employee];	
CREATE TABLE [Employee] (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (1, '鈴木', 'suzuki');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (3, '佐藤', 'sato');
Id Name Romaji
1 鈴木 suzuki
2 田中 tanaka
3 佐藤 sato

テーブルB [Employee_except]

DROP TABLE IF EXISTS [Employee_except];	
CREATE TABLE employee_except (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (1, '鈴木', 'suzuki');
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (3, '佐藤', 'sato');
Id Name Romaji
1 鈴木 suzuki
2 田中 tanaka
3 佐藤 sato

検証6-1

(	
    SELECT * FROM [Employee]	
    EXCEPT	
    SELECT * FROM [Employee_except]	
)	
UNION ALL	
(	
    SELECT * FROM [Employee_except]	
    EXCEPT	
    SELECT * FROM [Employee]	
)
Id Name Romaji

7. 完全一致の確認 (2/3)

テーブルA [Employee]

DROP TABLE IF EXISTS [Employee];	
CREATE TABLE [Employee] (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (1, '鈴木', 'suzuki');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (3, '佐藤', 'sato');
Id Name Romaji
1 鈴木 suzuki
2 田中 tanaka
3 佐藤 sato

テーブルB [Employee_except]

DROP TABLE IF EXISTS [Employee_except];	
CREATE TABLE employee_except (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (1, '鈴木', 'suzuki');
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
Id Name Romaji
1 鈴木 suzuki
2 田中 tanaka

検証7-1

(	
    SELECT * FROM [Employee]	
    EXCEPT	
    SELECT * FROM [Employee_except]	
)	
UNION ALL	
(	
    SELECT * FROM [Employee_except]	
    EXCEPT	
    SELECT * FROM [Employee]	
)
Id Name Romaji
3 佐藤 sato

8. 完全一致の確認 (3/3)

テーブルA [Employee]

DROP TABLE IF EXISTS [Employee];	
CREATE TABLE [Employee] (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (1, '鈴木', 'suzuki');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
INSERT INTO [Employee] ([Id], [Name], [Romaji]) VALUES (3, '佐藤', 'sato');
Id Name Romaji
1 鈴木 suzuki
2 田中 tanaka
3 佐藤 sato

テーブルB [Employee_except]

DROP TABLE IF EXISTS [Employee_except];	
CREATE TABLE employee_except (	
	[Id] int,
	[Name] nvarchar(255),
	[Romaji] nvarchar(255)
);
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (1, '鈴木', 'suzuki');
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (2, '田中', 'tanaka');
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (3, '佐藤', 'sato');
INSERT INTO [Employee_except] ([Id], [Name], [Romaji]) VALUES (4, '本田', 'honda');
Id Name Romaji
1 鈴木 suzuki
2 田中 tanaka
3 佐藤 sato
4 本田 honda

検証8-1

(	
    SELECT * FROM [Employee]	
    EXCEPT	
    SELECT * FROM [Employee_except]	
)	
UNION ALL	
(	
    SELECT * FROM [Employee_except]	
    EXCEPT	
    SELECT * FROM [Employee]	
)
Id Name Romaji
4 本田 honda
3
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?