SQL EXCEPT

SQL EXCEPT 是集合运算符之一，用于返回左侧查询的、未被右侧查询输出的唯一行。

换句话说，except 将返回左表中的所有记录，这些记录不存在于右侧。下图将帮助您理解 Except。

SQL Server EXCEPT 语法

Except 的语法如下所示

SELECT Column_Name1, Column_Name2 ......., Column_NameN FROM Table1
EXCEPT
SELECT Column_Name1, Column_Name2 ......., Column_NameN FROM Table2

Except 运算符遵循以下一系列规则：

  1. 两个查询中的总列数必须相同。
  2. 列数据类型必须相互兼容。
  3. 所有查询中的列顺序必须相同。
EXCEPT 1

在本示例中，我们将使用数据库中的两个表（Employ 和 Employees 2016）。从下面的屏幕截图中，您可以观察到 [Employ] 表有十条记录。

而 [Employees 2016] 表有四条记录。在本Server表中，只有一条唯一的记录 (9, Rob, Verhoff)，其余记录是相同的。

SQL Except 示例

以下查询将返回 Employ 表中不存在于 Employees2016 表中的所有记录，并显示结果。

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employ]
 EXCEPT
  SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2016]
SQL EXCEPT 3

让我们交换表的位置。这意味着此 except 查询将返回 Employees2016 表中不存在于 Employ 表中的所有记录。

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2016]
  EXCEPT
  SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employ]
EXCEPT 4

SQL Server EXCEPT WHERE 子句示例

如何将此运算符与WHERE 子句一起使用。在本例中，我们将连接两个语句。

  • 第一个结果集选择 Employ 表中年收入大于或等于 70000 的所有记录。
  • 第二个结果集选择 Employees 2016 表中的记录。
  • 此运算符选择第一个结果集中存在但第二个结果集中不存在的记录。
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employ]
WHERE [YearlyIncome] >= 70000
 EXCEPT
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employees 2016]
EXCEPT Where clause 5

Except 错误

以下查询将显示我们在编写 SQL Server except 查询时遇到的常见错误列表。为此，我们将使用数据库中的两个表（Employee 和 Employ）。我们的 [Employee] 表有 7 列和 14 行。

EXCEPT errors pf 9

让我们看看当我们在列数不相等的表上执行 except 操作时会发生什么。

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  EXCEPT
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employ]
Messages
--------
Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPToperator 
must have an equal number of expressions in their target lists.

现在，让我们修改查询以选择相同数量的列。

 SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  EXCEPT
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employ]
EXCEPT with an equal number of columns 7

从上图可以看出，它返回了七行，因为除了第 2、7、9、11 至 14 条记录外，Employee 表中的其余十条记录也存在于 Employ 表中。我们的运算符只选择 Employee 表中的唯一记录。