在 PostgreSQL 中，NOT IN 和 EXCEPT 都可以用于从一个结果集中排除某些行，但它们在实现方式、适用场景和性能表现上存在一些区别。以下是详细的对比：
- 语法和功能
SELECT column_name
FROM table_name
WHERE column_name NOT IN (SELECT column_name FROM another_table);
SELECT column_name
FROM table_name
EXCEPT
SELECT column_name
FROM another_table;
- 处理 NULL 的方式
- 性能表现
- 适用场景
示例
假设我们有两个表 employees 和 blacklist，需要找出不在黑名单中的员工。
使用 NOT IN：
sql复制
SELECT employee_id
FROM employees
WHERE employee_id NOT IN (SELECT employee_id FROM blacklist);
使用 EXCEPT：
sql复制
SELECT employee_id
FROM employees
EXCEPT
SELECT employee_id
FROM blacklist;
如果 blacklist 表中包含 NULL，NOT IN 查询将返回空结果，而 EXCEPT 查询仍然可以正确返回结果。
总结
场景 1：多条件筛选
假设我们有两个表 employees 和 contractors，分别存储员工和合同工的信息。我们需要找出在 employees 表中但不在 contractors 表中的员工信息，同时只选择特定部门（如 IT 部门）的员工。
sql复制
SELECT name, department
FROM employees
WHERE department = 'IT'
EXCEPT
SELECT name, department
FROM contractors
WHERE department = 'IT';
此查询返回在 IT 部门但不是合同工的员工信息。
场景 2：多表数据对比
假设我们有三个表：top_rated_films、most_popular_films 和 highest_revenue_films，分别存储评分最高的电影、最受欢迎的电影和收入最高的电影。我们需要找出那些评分最高但既不是最受欢迎也不是收入最高的电影。
sql复制
SELECT film_id, title
FROM top_rated_films
EXCEPT
SELECT film_id, title
FROM most_popular_films
EXCEPT
SELECT film_id, title
FROM highest_revenue_films;
此查询通过多次使用 EXCEPT，从 top_rated_films 中排除了同时出现在 most_popular_films 和 highest_revenue_films 中的电影。
场景 3：数据去重与差异分析
假设我们有两个表 table1 和 table2，存储了相似的数据，但可能存在重复或差异。我们需要找出在 table1 中但不在 table2 中的记录。
sql复制
SELECT column1, column2
FROM table1
EXCEPT
SELECT column1, column2
FROM table2;
此查询返回 table1 中独有的记录，适用于数据去重和差异分析。
场景 4：多字段比较
假设我们有两个表 employees 和 departments，分别存储员工信息和部门信息。我们需要找出那些员工地址和电话与部门地址和电话不匹配的记录。
sql复制
SELECT address, phone
FROM employees
EXCEPT
SELECT address, phone
FROM departments;
此查询通过比较多个字段，返回 employees 表中与 departments 表不匹配的地址和电话记录。
总结
EXCEPT 是 PostgreSQL 中用于集合操作的强大工具，特别适用于以下场景：
在 PostgreSQL 中，EXCEPT 是一个集合操作符，它要求两个查询的结果集在结构上是完全一致的，即列的数量、数据类型和顺序必须完全匹配。如果表结构不同，EXCEPT 无法直接使用。
为什么表结构必须一致？
EXCEPT 的工作原理是基于集合的差集操作，它会逐行比较两个查询的结果集。如果列的数量、类型或顺序不一致，PostgreSQL 会抛出错误，因为无法确定如何进行比较。
例如，以下查询会失败：
sql复制
SELECT column1, column2
FROM table1
EXCEPT
SELECT column3, column4
FROM table2;
如果 column1 和 column3 的类型不同，或者 column2 和 column4 的顺序不一致，PostgreSQL 会报错。
如果表结构不同怎么办？
如果需要使用 EXCEPT，但表结构不同，可以通过以下方法解决：
方法 1：显式指定列名和数据类型
如果两个表的列名或数据类型不同，可以通过显式指定列名和转换数据类型来解决。例如：
sql复制
SELECT column1 AS common_column, column2 AS common_column2
FROM table1
EXCEPT
SELECT column3::same_data_type AS common_column, column4::same_data_type AS common_column2
FROM table2;
这里，我们通过重命名列（AS common_column）和转换数据类型（::same_data_type）来确保两个查询的结果集结构一致。
方法 2：使用子查询或临时表
如果表结构差异较大，可以通过子查询或临时表将数据转换为一致的格式。例如：
sql复制
WITH normalized_table1 AS (
SELECT column1 AS common_column, column2 AS common_column2
FROM table1
),
normalized_table2 AS (
SELECT column3 AS common_column, column4 AS common_column2
FROM table2
)
SELECT common_column, common_column2
FROM normalized_table1
EXCEPT
SELECT common_column, common_column2
FROM normalized_table2;
这里，我们通过公共表表达式（CTE）将两个表的数据转换为一致的格式，然后使用 EXCEPT 进行差集操作。
方法 3：使用其他方法（如 NOT EXISTS 或 NOT IN）
如果表结构差异太大，无法通过上述方法解决，可以考虑使用其他方法，如 NOT EXISTS 或 NOT IN。这些方法不要求表结构一致，但可能需要额外的逻辑来实现相同的功能。
例如：
sql复制
SELECT column1, column2
FROM table1
WHERE (column1, column2) NOT IN (
SELECT column3, column4
FROM table2
);
或者：
sql复制
SELECT column1, column2
FROM table1 t1
WHERE NOT EXISTS (
SELECT 1
FROM table2 t2
WHERE t1.column1 = t2.column3 AND t1.column2 = t2.column4
);
总结
在实际应用中，选择哪种方法取决于具体需求和表结构的差异。
在 PostgreSQL 中，NOT EXISTS、NOT IN 和 EXCEPT 都可以用于从结果集中排除某些记录，但它们在语法、功能、性能和适用场景上存在显著区别。以下是它们的详细对比：
- 语法和功能
NOT EXISTS
SELECT column_name
FROM table_name t1
WHERE NOT EXISTS (
SELECT 1
FROM another_table t2
WHERE t1.some_column = t2.some_column
);
NOT IN
SELECT column_name
FROM table_name
WHERE some_column NOT IN (SELECT some_column FROM another_table);
EXCEPT
SELECT column_name
FROM table_name
EXCEPT
SELECT column_name
FROM another_table;
- 性能
NOT EXISTS
NOT IN
EXCEPT
- 适用场景
NOT EXISTS
NOT IN
EXCEPT
- 具体示例
假设我们有两个表 employees 和 blacklist，需要找出不在黑名单中的员工。
使用 NOT EXISTS
sql复制
SELECT employee_id, name
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM blacklist b
WHERE e.employee_id = b.employee_id
);
使用 NOT IN
sql复制
SELECT employee_id, name
FROM employees
WHERE employee_id NOT IN (SELECT employee_id FROM blacklist);
使用 EXCEPT
sql复制
SELECT employee_id, name
FROM employees
EXCEPT
SELECT employee_id, name
FROM employees
JOIN blacklist ON employees.employee_id = blacklist.employee_id;
- 总结
在实际应用中，可以根据具体需求、数据量和表结构选择合适的方法。
