SQL EXISTS

SQL EXISTS用来判断一个子查询是否返回至少一行结果。如果有结果,外面的查询继续,如果没有,外面的查询停止(无结果返回)。

SQL EXISTS 语法

SELECT 列1, 列2, ... 
FROM 表1
WHERE EXISTS
  (
    SELECT *
    FROM 表2
    WHERE 子查询条件
  )

SQL EXISTS 范例

数据表: Employees

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
204 Bhkki Hkooia Finance 45800
205 Uisaoo Dfaafk Finance 57000
206 Qidfa Srlkrt Finance 62000
302 Williaa Bdnaa Development 75000
303 Zkiil Errllov Development 55000
304 Uillaw Osk Development 49000

数据表: EmpAccess

EmployeeId AccessLevel
203 1
204 2
205 2
206 2
302 1
303 2
304 2

例1
我们想选择所有AccessLevel=1的职员(employee):

SELECT EmployeeId, FirstName, LastName
FROM Employees
WHERE EXISTS
  (
      SELECT EmployeeId
      FROM EmpAccess
      WHERE EmployeeId = Employees.EmployeeId
                AND AccessLevel = 1
  )

结果会类似于:

EmployeeId FirstName LastName
203 Mazojys Fxoj
302 Williaa Bdnaa

例2 (Example of SQL NOT EXISTS)
我们想选择所有的AccessLevel为1的职员(employee):

SELECT EmployeeId, FirstName, LastName
FROM Employees
WHERE NOT EXISTS
  (
      SELECT EmployeeId
      FROM EmpAccess
      WHERE EmployeeId = Employees.EmployeeId
                AND AccessLevel = 1
  )

结果会类似于:

EmployeeId FirstName LastName
204 Bhkki Hkooia
205 Uisaoo Dfaafk
206 Qidfa Srlkrt
303 Zkiil Errllov
304 Uillaw Osk