sql-intersect-clause

SQL - INTERSECT 运算符

在关系型数据库中，我们经常需要在两个不同的查询之间查找共同的记录。INTERSECT 运算符是标准 SQL 集合运算符，专为此目的而设计。它只返回同时出现在两个 SELECT 语句结果集中的行。

可以将其想象成查找两个维恩图 (Venn diagram) 的重叠部分。如果一个集合代表购买了产品 A 的客户，另一个集合代表购买了产品 B 的客户，那么 INTERSECT 将为您提供同时购买了这两种产品的客户列表。

理解 INTERSECT 运算符

INTERSECT 运算符结合两个 SELECT 语句，并且只返回两者共有的不同行。为了使其正常工作，必须遵循以下几个规则：

• 两个 SELECT 语句中的列数必须相同。
• 两个 SELECT 语句中列的顺序必须相同。
• 相应列的数据类型必须兼容（例如，您可以将 INT 与 SMALLINT 进行比较，但不能与 VARCHAR 进行比较）。

语法

INTERSECT 运算符的基本语法很简单：

SELECT column1, column2, ... FROM table1
[WHERE condition1]

INTERSECT

SELECT column1, column2, ... FROM table2
[WHERE condition2];

注意：INTERSECT 隐式地只返回不同的行。如果某行在第一个结果集中出现三次，在第二个结果集中出现两次，它在最终输出中将只出现一次。

实际示例：查找优秀员工

假设我们有两个表。ActiveEmployees 列出所有当前员工，QuarterlyHighPerformers 列出本季度超出目标的员工。我们想要找出同时是当前活跃员工的优秀员工。

首先，我们创建并填充表。

-- 所有当前活跃员工的表
CREATE TABLE ActiveEmployees (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100) NOT NULL,
    Department VARCHAR(50)
);

INSERT INTO ActiveEmployees (EmployeeID, FullName, Department) VALUES
(101, 'Alice Johnson', 'Sales'),
(102, 'Bob Williams', 'Engineering'),
(103, 'Charlie Brown', 'Sales'),
(104, 'Diana Miller', 'Marketing');

-- 本季度高绩效员工的表
CREATE TABLE QuarterlyHighPerformers (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100) NOT NULL,
    Department VARCHAR(50)
);

INSERT INTO QuarterlyHighPerformers (EmployeeID, FullName, Department) VALUES
(101, 'Alice Johnson', 'Sales'),
(103, 'Charlie Brown', 'Sales'),
(105, 'Eve Davis', 'Engineering'); -- Eve 是一位前员工

现在，我们使用 INTERSECT 查找同时存在于这两个表中的员工：

SELECT EmployeeID, FullName, Department FROM ActiveEmployees
INTERSECT
SELECT EmployeeID, FullName, Department FROM QuarterlyHighPerformers;

输出

查询只返回同时存在于两个列表中的员工：

员工ID	全名	部门
101	Alice Johnson	Sales
103	Charlie Brown	Sales

将 INTERSECT 与其他子句结合使用

您可以将 WHERE、ORDER BY 和其他子句与 INTERSECT 结合使用。WHERE 子句在计算交集之前单独应用于每个 SELECT 语句。ORDER BY 子句可以添加到最后以对最终结果集进行排序。

带有 WHERE 的示例

让我们查找专门在“Sales”部门的活跃高绩效员工。

SELECT EmployeeID, FullName, Department FROM ActiveEmployees
WHERE Department = 'Sales'

INTERSECT

SELECT EmployeeID, FullName, Department FROM QuarterlyHighPerformers
WHERE Department = 'Sales';

此查询将产生与之前相同的结果，但它在大型表上可能更高效，因为它在执行交集操作之前会先筛选数据。

处理不支持 INTERSECT 的数据库（例如 MySQL）

一些流行的数据库系统，如 MySQL（8.0 版本之前），不支持 INTERSECT 运算符。在这种情况下，您可以使用 INNER JOIN 或 IN 运算符来实现相同的结果。

替代方案 1：使用 INNER JOIN

INNER JOIN 是一种查找匹配行的常见且高效的方法。

SELECT DISTINCT T1.EmployeeID, T1.FullName, T1.Department
FROM ActiveEmployees AS T1
INNER JOIN QuarterlyHighPerformers AS T2 ON T1.EmployeeID = T2.EmployeeID;

这里的 DISTINCT 关键字至关重要，因为 INTERSECT 返回唯一行，而 JOIN 如果存在多个匹配项可能会产生重复项（尽管在此特定主键示例中不会）。

替代方案 2：使用 IN 搭配子查询

当您只需检查另一个表中是否存在某个键时，这种方法通常更具可读性。

SELECT EmployeeID, FullName, Department
FROM ActiveEmployees
WHERE EmployeeID IN (SELECT EmployeeID FROM QuarterlyHighPerformers);

常见错误和调试

• 列不匹配： 最常见的错误是 SELECT 列表中列的数量不同或数据类型不兼容。务必仔细检查所选列是否完全对齐。
• 性能问题： 在非常大的数据集上，INTERSECT 可能会很慢，因为数据库需要处理和比较两个完整的结果集。如果性能至关重要，测试等效的 INNER JOIN 或 EXISTS 查询是一个很好的实践。使用数据库的 EXPLAIN 命令来比较查询计划。
• 忘记替代方案： 习惯于 PostgreSQL 或 SQL Server 的开发人员可能会忘记 INTERSECT 并非普遍支持。了解 JOIN 或 IN 替代方案对于编写可移植代码至关重要。