在PG数据库中，not in 和except的区别

在 PostgreSQL 中，NOT IN 和 EXCEPT 都可以用于从一个结果集中排除某些行，但它们在实现方式、适用场景和性能表现上存在一些区别。以下是详细的对比：

1. 语法和功能

NOT IN

NOT IN 是一个条件语句，用于从一个查询结果中排除子查询返回的值。它的语法如下：

sql复制

SELECT column_name

FROM table_name

WHERE column_name NOT IN (SELECT column_name FROM another_table);

它的作用是筛选出主查询中不在子查询结果中的行。

EXCEPT

EXCEPT 是一个集合操作符，用于从两个查询结果集中返回第一个结果集中存在但第二个结果集中不存在的行。它的语法如下：

sql复制

SELECT column_name

FROM table_name

EXCEPT

SELECT column_name

FROM another_table;

它直接比较两个结果集，并返回差集。

1. 处理 NULL 的方式

NOT IN

如果子查询中包含 NULL，NOT IN 会导致查询结果为空，因为 NULL 的比较在 SQL 中是不确定的。

EXCEPT

EXCEPT 不受 NULL 的影响，因为它基于集合的差集操作，会正确处理包含 NULL 的情况。

1. 性能表现

NOT IN

在处理大数据量时，NOT IN 的性能可能会下降，因为它需要逐一比较主查询和子查询的结果。

EXCEPT

EXCEPT 通常在处理大数据集时表现更好，因为它利用了数据库的优化技术来高效处理集合操作。

1. 适用场景

NOT IN

适用于简单的单列筛选场景，逻辑直观，适合数据量较小的情况。

EXCEPT

更适合复杂的多列比较或集合操作，尤其是在需要处理多个字段或大数据集时。

示例

假设我们有两个表 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 查询仍然可以正确返回结果。

总结

如果需要处理包含 NULL 的数据或进行复杂的集合操作，推荐使用 EXCEPT。

如果数据量较小且逻辑简单，可以使用 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

);

总结

EXCEPT 要求两个查询的结果集在结构上完全一致。

如果表结构不同，可以通过显式指定列名、转换数据类型、使用子查询或临时表来解决。

如果无法通过上述方法解决，可以考虑使用 NOT EXISTS 或 NOT IN 等其他方法。

在实际应用中，选择哪种方法取决于具体需求和表结构的差异。

在 PostgreSQL 中，NOT EXISTS、NOT IN 和 EXCEPT 都可以用于从结果集中排除某些记录，但它们在语法、功能、性能和适用场景上存在显著区别。以下是它们的详细对比：

1. 语法和功能

NOT EXISTS

语法：

sql复制

SELECT column_name

FROM table_name t1

WHERE NOT EXISTS (

SELECT 1

FROM another\_table t2

WHERE t1.some\_column = t2.some\_column

);

功能：

NOT EXISTS 是一个子查询操作符，用于检查子查询是否返回任何行。

如果子查询没有返回行，则主查询的当前行会被保留。

它通常用于基于相关子查询的条件筛选。

NOT IN

语法：

sql复制

SELECT column_name

FROM table_name

WHERE some_column NOT IN (SELECT some_column FROM another_table);

功能：

NOT IN 是一个子查询操作符，用于从主查询中排除子查询返回的值。

它基于值的比较，适用于单列筛选。

如果子查询返回 NULL，NOT IN 的结果会为空。

EXCEPT

语法：

sql复制

SELECT column_name

FROM table_name

EXCEPT

SELECT column_name

FROM another_table;

功能：

EXCEPT 是一个集合操作符，用于计算两个查询结果集的差集。

它直接比较两个结果集，并返回第一个结果集中独有的记录。

不受 NULL 的影响，但要求两个查询的结果集结构完全一致。

1. 性能

NOT EXISTS

通常被认为是最高效的，因为它会在子查询中找到第一个匹配的行后立即停止搜索。

特别适用于子查询返回大量数据时，因为它不需要生成完整的子查询结果集。

NOT IN

性能通常取决于子查询的结果集大小。

如果子查询返回大量数据，可能会导致性能问题。

如果子查询返回 NULL，NOT IN 的结果会为空，这可能是一个陷阱。

EXCEPT

性能通常较好，因为它基于集合操作，数据库会对其进行优化。

适用于复杂查询和多列比较场景，但要求两个查询的结果集结构一致。

1. 适用场景

NOT EXISTS

适用于基于相关子查询的条件筛选。

适合子查询返回大量数据的场景。

例如：查找没有匹配记录的行。

NOT IN

适用于简单的单列值比较。

适合子查询返回少量数据的场景。

如果子查询可能返回 NULL，应避免使用 NOT IN。

EXCEPT

适用于复杂的集合操作，特别是多列比较。

适合需要从两个查询结果中计算差集的场景。

例如：比较两个表或查询结果的差异。

1. 具体示例

假设我们有两个表 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

);

解释：检查 employees 中的每个员工是否在 blacklist 中，如果不在，则保留。

使用 NOT IN

sql复制

SELECT employee_id, name

FROM employees

WHERE employee_id NOT IN (SELECT employee_id FROM blacklist);

解释：从 employees 中排除 blacklist 中的员工。

注意：如果 blacklist 中有 NULL，结果会为空。

使用 EXCEPT

sql复制

SELECT employee_id, name

FROM employees

EXCEPT

SELECT employee_id, name

FROM employees

JOIN blacklist ON employees.employee_id = blacklist.employee_id;

解释：从 employees 中排除与 blacklist 中匹配的员工。

注意：EXCEPT 要求两个查询的结果集结构一致。

1. 总结

NOT EXISTS：

优点：通常最高效，适用于子查询返回大量数据的场景。

缺点：语法稍复杂，需要理解相关子查询。

NOT IN：

优点：语法简单，适用于单列值比较。

缺点：如果子查询返回 NULL，结果会为空。

EXCEPT：

优点：适用于复杂的集合操作和多列比较。

缺点：要求两个查询的结果集结构一致。

在实际应用中，可以根据具体需求、数据量和表结构选择合适的方法。

————————————————

                        版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/Vic10101/article/details/146016052