Except演算子の仕様について

![Untitled.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287622/bb31ba36-0aae-721e-2215-aef5235701e1.png)

# 背景

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

# 基本仕様

SQL Server の Except演算子

- テーブルAで取得した行(レコード)とテーブルBで取得した行を全ての列(カラム)の値で比較し、列の値がすべて一致する行は抽出しません。
- テーブルAのみにある行(レコード)を抽出します。
- 2つのテーブルの列数が同じであることが必要です。
- 列名は異なっていても比較できます。
- 集合演算子で、差集合を求めます。
 
 ![Untitled 1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287622/d518a944-9d23-6192-79ba-da7f41b6e088.png)

# 仕様確認

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

テーブルA [Employee]

```sql
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]

```sql
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

```sql
SELECT * FROM [Employee]
EXCEPT
SELECT * FROM [Employee_except]
```

| Id   | Name | Romaji |
| ---- | ---- | ----- |
| 1    | 鈴木 | suzuki |
| 3    | 佐藤 | sato   |

### 検証1-2

```sql
SELECT * FROM [Employee_except]
EXCEPT
SELECT * FROM [Employee]
```

| Id   | Name | Romaji |
| ---- | ---- | ----- |
|  |  |  |

### 検証1-3

```sql
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]

```sql
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]

```sql
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

```sql
SELECT * FROM [Employee]
EXCEPT
SELECT * FROM [Employee_except]
```

| Id   | Name | Romaji |
| ---- | ---- | ----- |
| 1    | 鈴木 | suzuki |
| 2    | 田中 | NULL |
| 3    | 佐藤 | sato   |

### 検証2-2

```sql
SELECT * FROM [Employee_except]
EXCEPT
SELECT * FROM [Employee]
```

| Id   | Name | Romaji |
| ---- | ---- | ----- |
| 2    | 田中 | tanaka |

## 3. 両集合のヌル確認

テーブルA [Employee]

```sql
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]

```sql
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

```sql
SELECT * FROM [Employee]
EXCEPT
SELECT * FROM [Employee_except]
```

| Id   | Name | Romaji |
| ---- | ---- | ----- |
| 1    | 鈴木 | suzuki |
| 3    | 佐藤 | sato   |

### 検証3-2

```sql
SELECT * FROM [Employee_except]
EXCEPT
SELECT * FROM [Employee]
```

| Id   | Name | Romaji |
| ---- | ---- | ----- |
|  |  |  |

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

テーブルA [Employee]

```sql
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]

```sql
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

```sql
SELECT * FROM [Employee]
EXCEPT
SELECT * FROM [Employee_except]
```

| Id   | Name2 | Romaji |
| ---- | ---- | ----- |
| 1    | 鈴木 | suzuki |
| 3    | 佐藤 | sato   |

### 検証4-2

```sql
SELECT * FROM [Employee_except]
EXCEPT
SELECT * FROM [Employee]
```

| Id   | Name | Romaji |
| ---- | ---- | ----- |
|  |  |  |

## 5. 重複レコードの確認

テーブルA [Employee]

```sql
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]

```sql
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

```sql
SELECT * FROM [Employee]
EXCEPT
SELECT * FROM [Employee_except]
```

| Id   | Name | Romaji |
| ---- | ---- | ----- |
| 1    | 鈴木 | suzuki |
| 3    | 佐藤 | sato   |

### 検証5-2

```sql
SELECT * FROM [Employee_except]
EXCEPT
SELECT * FROM [Employee]
```

| Id   | Name | Romaji |
| ---- | ---- | ----- |
|  |  |  |

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

テーブルA [Employee]

```sql
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]

```sql
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

```sql
(	
    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]

```sql
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]

```sql
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

```sql
(	
    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]

```sql
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]

```sql
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

```sql
(	
    SELECT * FROM [Employee]	
    EXCEPT	
    SELECT * FROM [Employee_except]	
)	
UNION ALL	
(	
    SELECT * FROM [Employee_except]	
    EXCEPT	
    SELECT * FROM [Employee]	
)	

```

| Id   | Name | Romaji |
| ---- | ---- | ----- |
| 4    | 本田 | honda  |![Untitled.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287622/aaa24570-9714-b27c-1000-be6a47788ba1.png)