SQL NOT EXISTS 运算符

SQL NOT EXISTS 运算符的作用与 EXISTS 运算符的作用正好相反。它用于限制 SELECT 语句返回的行数。

NOT EXISTS 运算符将检查子查询是否存在行，如果不存在行，则返回 TRUE，否则返回 FALSE。或者我们可以简单地说，Not Exists 运算符返回的结果与子查询返回的结果完全相反。

在深入此示例之前，我建议您参考 SQL 子查询 文章以了解子查询的设计和查询解析。

SQL NOT EXISTS 语法

可以在 Server 中使用 NOT EXISTS 的基本语法可以写成：

SELECT [Column Names]
FROM [Source]
WHERE NOT EXISTS (Write Subquery to Check)
  • 列：它允许我们选择表中的列数。可以是一列或多列。
  • 源：数据库中的一个或多个表。SQL JOINS 用于连接多个表。
  • 子查询：在这里我们必须提供子查询。如果子查询返回 true，则它将返回记录，否则它将不返回任何记录。

在本文中，我们将向您展示如何使用 SQL Server NOT EXISTS 运算符及示例。为此，我们将使用我们在 NOT IN 运算符 文章中提到的表数据。

SQL NOT EXISTS 示例 1

以下查询将查找 Employees 表中 [Sales] 小于 1000 的所有员工

USE [SQL Tutorial]
GO
SELECT Employ1.[EmpID]
      ,Employ1.[FirstName] + ' ' + Employ1.[LastName] AS [Full Name]
      ,Employ1.[Education]
      ,Employ1.[Occupation]
      ,Employ1.[YearlyIncome]
      ,Employ1.[Sales]
      ,Employ1.[HireDate]
  FROM [Employee] AS Employ1
  WHERE NOT EXISTS( SELECT * FROM [Employee] AS Employ2 
		    WHERE Employ1.[EmpID] = Employ2.[EmpID] 
			 AND [Sales] > 1000
		  )

请参考最后一张图片，查看上述查询返回的结果集。

我将 Not Exists 条件更改为 Sales < 10000，这意味着子查询将返回所有可用的行。而 NOT EXISTS 将返回零记录，因为它返回子查询的精确相反结果。

USE [SQL Tutorial]
GO
SELECT Employ1.[EmpID]
      ,Employ1.[FirstName] + ' ' + Employ1.[LastName] AS [Full Name]
      ,Employ1.[Education]
      ,Employ1.[Occupation]
      ,Employ1.[YearlyIncome]
      ,Employ1.[Sales]
      ,Employ1.[HireDate]
  FROM [Employee] AS Employ1
  WHERE NOT EXISTS( SELECT * FROM [Employee] AS Employ2 
		    WHERE Employ1.[EmpID] = Employ2.[EmpID] 
			 AND [Sales] < 10000
		  )

它会返回一个空的表，并附带以下消息。

(0 rows affected)

如您所见，查询返回了空记录，因为子查询返回 TRUE，而 Not exists 将返回 false。为了更好地理解，让我们再展示一个示例。

我将条件更改为 Sales > 10000，这是一个假条件。因此，NOT EXISTS 运算符将返回所有记录。出于演示目的，我们在图像中使用了 Sales > 3000。

USE [SQL Tutorial]
GO
SELECT Employ1.[EmpID]
      ,Employ1.[FirstName] + ' ' + Employ1.[LastName] AS [Full Name]
      ,Employ1.[Education]
      ,Employ1.[Occupation]
      ,Employ1.[YearlyIncome]
      ,Employ1.[Sales]
      ,Employ1.[HireDate]
  FROM [Employee] AS Employ1
  WHERE NOT EXISTS( SELECT * FROM [Employee] AS Employ2 
		    WHERE Employ1.[EmpID] = Employ2.[EmpID] 
			 AND [Sales] > 10000
		  )

从下面的截图可以看出，它返回了所有 sales 不大于 3000 的行。因为子查询返回 FALSE，这意味着 NOT EXISTS 将返回 TRUE。

Not Exists 示例 2

以下 SQL Server Not Exists 查询将查找职业既非熟练技工也非文员的员工。在这里，我们将在 子查询 中使用 IN 运算符。

USE [SQL Tutorial]
GO
SELECT Employ1.[EmpID]
      ,Employ1.[FirstName] + ' ' + Employ1.[LastName] AS [Full Name]
      ,Employ1.[Education]
      ,Employ1.[Occupation]
      ,Employ1.[YearlyIncome]
      ,Employ1.[Sales]
      ,Employ1.[HireDate]
  FROM [Employee] AS Employ1
  WHERE NOT EXISTS( SELECT * FROM [Employee] AS Employ2 
		    WHERE Employ1.[EmpID] = Employ2.[EmpID] 
			AND [Occupation] IN ('Skilled Manual', 'Clerical')
		   )

输出

NOT EXISTS Operator Example