SQL EXISTS 运算符

SQL EXISTS 运算符用于限制 SELECT 语句返回的行数。EXISTS 运算符检查子查询是否存在行，如果存在，则返回 TRUE，否则返回 FALSE。

SQL Server EXISTS 运算符的基本语法可以这样写：

SELECT [Column Names]
FROM [Source]
WHERE EXISTS (Write Subquery to Check)

• 列：这允许我们选择表中的列数。可以是一列或多列。
• 源：数据库中存在一个或多个表。JOIN 用于连接多个表。
• 子查询：在这里，我们必须提供子查询。如果子查询返回 true，则返回记录。否则，Server 不返回任何记录。

对于这个 EXISTS 运算符演示，我们将使用下面显示的数据

SQL EXISTS 运算符示例

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

提示：在开始这个 Exists 运算符示例之前，我建议您参考子查询文章来理解子查询的设计和查询解析。

SELECT EMP1.[EmpID]
      ,EMP1.[FirstName]
      ,EMP1.[LastName]
      ,EMP1.[Education]
      ,EMP1.[Occupation]
      ,EMP1.[YearlyIncome]
      ,EMP1.[Sales]
      ,EMP1.[HireDate]
  FROM [Employee] AS EMP1
  WHERE EXISTS( SELECT * FROM [Employee] AS EMP2 
		WHERE EMP1.[EmpID] = EMP2.[EmpID] 
			AND [Sales] > 1000
		)

让我向您展示子查询的结果。

让我们将条件更改为 Sales = 1000，这是一个假条件

SELECT EMP1.[EmpID]
      ,EMP1.[FirstName]
      ,EMP1.[LastName]
      ,EMP1.[Education]
      ,EMP1.[Occupation]
      ,EMP1.[YearlyIncome]
      ,EMP1.[Sales]
      ,EMP1.[HireDate]
  FROM [Employee] AS EMP1
  WHERE EXISTS( SELECT * FROM [Employee] AS EMP2 
		WHERE EMP1.[EmpID] = EMP2.[EmpID] 
			AND [Sales] = 1000
		)

正如您所见，查询返回空记录，因为子查询返回 false。为了更好地理解，让我们向您展示另一个 Exists 运算符示例。

以下查询将查找表中 [Occupation] 等于 Management 的所有员工

SELECT EMP1.[EmpID]
      ,EMP1.[FirstName]
      ,EMP1.[LastName]
      ,EMP1.[Education]
      ,EMP1.[Occupation]
      ,EMP1.[YearlyIncome]
      ,EMP1.[Sales]
      ,EMP1.[HireDate]
  FROM [Employee] AS EMP1
  WHERE EXISTS( SELECT * FROM [Employee] AS EMP2 
		WHERE EMP1.[EmpID] = EMP2.[EmpID] 
			AND [Occupation] = 'Management'
		)

以下 SQL Server exists 运算符查询将查找表中 Occupation 为 Management、Professional 或 Clerical 的所有人。

在这里，我们将在子查询中使用IN 运算符。

SELECT EMP1.[EmpID]
      ,EMP1.[FirstName]
      ,EMP1.[LastName]
      ,EMP1.[Education]
      ,EMP1.[Occupation]
      ,EMP1.[YearlyIncome]
      ,EMP1.[Sales]
      ,EMP1.[HireDate]
  FROM [Employee] AS EMP1
  WHERE EXISTS( SELECT * FROM [Employee] AS EMP2 
		WHERE EMP1.[EmpID] = EMP2.[EmpID] 
		  AND [Occupation] IN ('Management', 'Professional', 'Clerical')
		)

EXISTS 示例 4

您可能想知道为什么我在子查询中使用 EMP1.[EmpID] = EMP2.[EmpID]。所以让我删除那一行，看看结果集。

SELECT EMP1.[EmpID]
      ,EMP1.[FirstName]
      ,EMP1.[LastName]
      ,EMP1.[Education]
      ,EMP1.[Occupation]
      ,EMP1.[YearlyIncome]
      ,EMP1.[Sales]
      ,EMP1.[HireDate]
  FROM [Employee] AS EMP1
  WHERE EXISTS( SELECT * FROM [Employee] AS EMP2 
		WHERE [Occupation] IN ('Management', 'Professional', 'Clerical')
		)

从下面的截图可以看到，它返回所选表中的所有记录。因为子查询返回 TRUE