子查询

 数据库   ZeroIsStart   2024-10-20 09:56   19
  从入门到精通SQL Server

SQL Server 子查询详解：简单子查询与相关子查询

在 SQL 查询中，子查询（Subquery）是一个嵌套在另一个查询中的查询。子查询可以帮助我们从数据库中获取特定数据，作为外部查询的输入。SQL Server 提供了两种常见的子查询类型：简单子查询（Non-Correlated Subquery）和相关子查询（Correlated Subquery）。本文将详细介绍这两种子查询的使用方法，并通过具体示例展示它们的应用场景、语法和常见的使用技巧。

1. 简单子查询（Non-Correlated Subquery）

简单子查询是指在外部查询中使用的子查询，其中的子查询与外部查询没有依赖关系。简单子查询是独立的查询，其结果可以直接作为外部查询的输入，通常用于 WHERE、HAVING 或 SELECT 子句中。

语法：

SELECT 列名
FROM 表名
WHERE 列名 运算符 (SELECT 列名 FROM 表名 WHERE 条件);

示例：

假设我们有两个表：Employees 和 Departments。现在我们要查询所有薪资高于公司平均薪资的员工。

SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

解释：

  • 外部查询选择了 Employees 表中的员工数据。
  • 内部查询计算了 Employees 表的平均薪资。
  • 外部查询的 WHERE 子句通过子查询的结果进行筛选，找出所有薪资高于平均薪资的员工。

应用场景：

  • 查询特定条件下的记录，如查询大于、等于或小于某个值的记录。
  • 当需要通过聚合函数（如 AVG、SUM）计算出结果后，作为筛选条件来使用时。

2. 相关子查询（Correlated Subquery）

相关子查询是指在外部查询和内部查询之间存在依赖关系的子查询。也就是说，子查询中的每一行数据都会依赖于外部查询中的当前行。相关子查询通常会出现在 WHERE 子句中，并且在每次外部查询处理一行数据时，都会执行一次子查询。

语法：

SELECT 列名
FROM 表名 外部表
WHERE 列名 运算符 (SELECT 列名 FROM 表名 内部表 WHERE 内部表.列名 = 外部表.列名);

示例：

假设我们仍然使用 Employees 和 Departments 表，现在我们要查询所有员工及其所属部门的所有薪资大于该员工薪资的员工。

SELECT E.EmployeeID, E.Name, E.Salary
FROM Employees E
WHERE E.Salary > (SELECT MAX(E2.Salary)
                  FROM Employees E2
                  WHERE E2.DepartmentID = E.DepartmentID);

解释：

  • 外部查询：查询所有员工的 EmployeeID、Name 和 Salary。
  • 内部查询：对于每个员工，查找其所属部门中薪资最高的员工。
  • 相关性：在子查询中，E2.DepartmentID = E.DepartmentID 使得子查询对于每个员工（外部查询中的行）都被执行一次。即，子查询是与外部查询的每一行相关的。

应用场景：

  • 查询需要与外部查询中的每一行进行匹配和比较的情况。
  • 通过对比外部查询中的行与内部查询中相关的数据来获取结果。例如，查询大于某个条件的员工、日期范围内的记录等。

3. 子查询与 IN 操作符结合使用

在一些情况下，我们可以使用子查询与 IN 操作符结合使用，以便从外部查询中筛选出多个值。IN 操作符可以接受一个子查询，并返回所有符合条件的记录。

语法：

SELECT 列名
FROM 表名
WHERE 列名 IN (SELECT 列名 FROM 表名 WHERE 条件);

示例：

我们要查询所有来自于特定部门的员工。首先，我们可以查询所有属于 IT 和 HR 部门的员工。

SELECT EmployeeID, Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID
                        FROM Departments
                        WHERE DepartmentName IN ('IT', 'HR'));

解释：

  • 内部查询（子查询）返回所有 IT 和 HR 部门的 DepartmentID。
  • 外部查询根据子查询的结果，筛选出所有来自这些部门的员工。

应用场景：

  • 当需要对多个值进行匹配时，例如筛选多个部门、多个日期、多个员工等。

4. 子查询与 EXISTS 操作符结合使用

EXISTS 操作符用于测试子查询是否返回至少一行数据。如果子查询返回了数据，则 EXISTS 为真，外部查询中的相关条件也会成立。

语法：

SELECT 列名
FROM 表名
WHERE EXISTS (SELECT 1 FROM 表名 WHERE 条件);

示例：

假设我们要查询所有至少有一名员工的部门。

SELECT DepartmentID, DepartmentName
FROM Departments
WHERE EXISTS (SELECT 1
              FROM Employees
              WHERE Employees.DepartmentID = Departments.DepartmentID);

解释：

  • 子查询检查每个部门是否有员工。
  • EXISTS 操作符确保只有那些至少有一名员工的部门会出现在外部查询的结果中。

应用场景：

  • 当需要检查子查询中是否存在数据时，通常用于检查是否有匹配的记录。
  • 比如检查某个表中是否有相关的记录存在，从而决定是否查询或操作。

5. 子查询与 NOT EXISTS 操作符结合使用

NOT EXISTS 操作符与 EXISTS 相反，用于测试子查询是否不返回任何数据。如果子查询不返回数据，则 NOT EXISTS 为真，外部查询中的相关条件会成立。

语法：

SELECT 列名
FROM 表名
WHERE NOT EXISTS (SELECT 1 FROM 表名 WHERE 条件);

示例：

假设我们要查询没有任何员工的部门。

SELECT DepartmentID, DepartmentName
FROM Departments
WHERE NOT EXISTS (SELECT 1
                  FROM Employees
                  WHERE Employees.DepartmentID = Departments.DepartmentID);

解释：

  • 子查询检查是否存在与部门相关的员工。
  • NOT EXISTS 确保只有那些没有员工的部门会出现在外部查询的结果中。

应用场景：

  • 当需要查询某些条件下不存在关联记录的情况时。比如查找没有销售的产品、没有参与项目的员工等。

6. 子查询中的多重筛选条件

子查询不仅可以进行简单的比较，还可以通过结合多个条件来进行更复杂的筛选。

示例：

假设我们想查询所有薪资高于公司平均薪资且在 HR 部门的员工。

SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees)
AND DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR');

解释：

  • 外部查询选择了所有员工数据。
  • 第一个子查询计算了公司所有员工的平均薪资，作为筛选条件。
  • 第二个子查询返回 HR 部门的 DepartmentID，并用它作为进一步筛选的条件。

总结

  • 简单子查询：独立的查询，不依赖外部查询。适用于查询单一值的场景，如筛选大于某个数值的记录。
  • 相关子查询：子查询依赖于外部查询中的每一行数据。适用于在子查询中依赖外部查询结果的情况，如比较员工薪资与部门中最高薪资的关系。
  • IN 子查询：用于查询与子查询返回的多个值匹配的记录。
  • EXISTS 和 NOT EXISTS：检查子查询是否返回至少一行记录，适用于存在或不存在关联数据时的筛选。

通过理解子查询的不同类型和适用场景，开发者可以在 SQL 查询中更灵活地处理复杂的数据筛选和分析问题。在实际应用中，根据查询需求选择合适的子查询类型，可以提高查询的准确性和性能。