子查询和 IN 测试

选择领导运输部门或财务部门的雇员的姓名：

SELECT GivenName, Surname
FROM Employees
WHERE EmployeeID IN (
   SELECT DepartmentHeadID
   FROM Departments
   WHERE ( DepartmentName='Finance' OR 
          DepartmentName = 'Shipping' ) );

GivenName	Surname
Mary Anne	Shea
Jose	Martinez

此示例中的子查询从 Departments 表提取与运输部和财务部领导对应的 ID 号。主查询随后返回 ID 号与该子查询找到的两个 ID 号中的一个匹配的雇员的姓名。

SELECT DepartmentHeadID
FROM Departments
WHERE ( DepartmentName='Finance' OR 
       DepartmentName = 'Shipping' );

子查询集合成员资格测试等同于 =ANY 测试。以下查询等同于上面示例中的查询。

SELECT GivenName, Surname
FROM Employees
WHERE EmployeeID = ANY (
   SELECT DepartmentHeadID
   FROM Departments
   WHERE ( DepartmentName='Finance' OR 
          DepartmentName = 'Shipping' ) );

您还可以使用子查询集合成员资格测试来提取列值与由子查询生成的任何值均不相等的那些行。要对集合成员资格测试取非，请在关键字 IN 之前插入 NOT 一词。

下面的查询中的子查询返回不是财务部门或运输部门领导的雇员的名和姓。

SELECT GivenName, Surname
FROM Employees
WHERE EmployeeID NOT IN (
   SELECT DepartmentHeadID
   FROM Departments
   WHERE ( DepartmentName='Finance' OR 
          DepartmentName = 'Shipping' ) );

子查询和 IN 测试

示例

集合成员资格测试等效于 =ANY 测试

集合成员资格测试的取非

示例