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

已于 2025-03-16 17:35:38 修改
阅读量784 收藏 4
点赞数 10
分类专栏： Mybatis SQL学习 文章标签： 数据库
于 2025-03-04 14:52:32 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Vic10101/article/details/146016052
版权

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

1. 语法和功能

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

    SELECT column_name
FROM table_name
WHERE column_name NOT IN (SELECT column_name FROM another_table);

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

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

    SELECT column_name
FROM table_name
EXCEPT
SELECT column_name
FROM another_table;

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

2. 处理 NULL 的方式

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

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

3. 性能表现

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

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

4. 适用场景

  • 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，分别存储评分最高的电影、最受欢迎的电影和收入最高的电影。我们需要找出那些评分最高但既不是最受欢迎也不是收入最高的电影。

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 中用于集合操作的强大工具，特别适用于以下场景：

  1. 多条件筛选：从一个结果集中排除满足多个条件的记录。

  2. 多表数据对比：比较多个表之间的差异。

  3. 数据去重与差异分析：找出一个表中独有的记录。

  4. 多字段比较：比较多个字段的差异。

在 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 的影响，但要求两个查询的结果集结构完全一致。

2. 性能

NOT EXISTS

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

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

NOT IN

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

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

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

EXCEPT

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

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

3. 适用场景

NOT EXISTS

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

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

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

NOT IN

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

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

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

EXCEPT

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

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

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

4. 具体示例

假设我们有两个表 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 要求两个查询的结果集结构一致。

5. 总结

  • NOT EXISTS：

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

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

  • NOT IN：

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

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

  • EXCEPT：

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

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

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



 

PostgreSQL反连接not in优化
Focus on PostgreSQL
03-08 3737
两表关联只返回主表与子表没关联上的数据，这种情况即反连接，一般指的是not in和not exsits。 在实际应用中，我们常常会遇到这样的两表关联的情况，想要查看一张表的某个列没有出现在另一张表中的数据，下面我们来看看在PostgreSQL中这种场景我们有哪些优化方法呢？ 例子： –建表插入数据： 说明：a表1000条数据，b表100w条数据，我们想要判断a表中的id在b表的aid中不存在的数据。 create table a(id int primary key, info text); creat
PostgreSQL SQL优化之NOT IN问题
dimawei2225的博客
07-26 3461
在我们平时写SQL时，如果遇到需要排除某些数据时，往往使用id <> xxx and id <> xxx，进而改进为id not in (xxx, xxx); 这样写没有问题，而且简化了SQL，但是往往有些极端情况，使用not in就会造成极大的性能损耗，例如： select * from test where id not in (select ...
SQL中EXCEPT和Not in的区别？
u010227447的专栏
04-03 816
初始化两张表： CREATE TABLE tb1(ID int) INSERT tb1          SELECT NULL UNION  ALL          SELECT NULL UNION  ALL          SELECT NULL UNION  ALL          SELECT 1 UNION  ALL          SELECT 2 UNION
SQL中EXCEPT和Not in的区别
苦苦的潜行者
02-04 4473
[转自]http://www.zxbc.cn/html/20070621/23177.html SQL中EXCEPT和Not in的区别？ 我们一起来看看。 初始化两张表： CREATE TABLE tb1(ID int)  INSERT tb1          SELECT NULL UNION  ALL          SELECT NULL UNION  ALL
postgreSql not in 问题记录
胡萧徒的博客
12-16 1725
not in 后面的集合中有null值,会导致查询失效
ConnectPG_连接PG数据库函数_
09-29
在上述代码中，我们定义了一个名为`connect_to_pg_db`的函数，该函数创建了一个到PG数据库的连接，并通过游标执行了SQL查询。请注意替换占位符为实际的数据库信息。 `Attlist.txt`、`Attlist - 副本.txt`和`...
【数据库学习】Postgres（PG）概览与PostgreSQL基本语法
热门推荐
午后阳光
01-27 2万+
-数组可以定义长度、也可以不给。也可以定义多维数组。但定义长度和多维数组在实际使用中是无效的。如下面两种定义是等价的。类似于C的结构体，
python访问pg数据库，并将查询结果保存到文件（txt，word，Excel）
weixin_43748334的博客
10-27 818
分享一下python读取PostgreSQL数据库中的数据，并保存到对应路径的文档中
PostgreSQL 表空间、数据库、模式、角色和用户之间的关系梳理
老苏畅谈运维
05-30 2247
不同的数据库表空间有不同的定义。在postgres中，表空间允许在文件系统中定义数据库对象存储的位置，实质上就是指定一个目录。应用场景：存储磁盘没有空间时，可以使用表空间把数据存在其它地方；利用表空间对数据库进行性能优化。常用来将频繁使用的数据表或者索引放在高性能的硬盘上，而较少使用的放在普通硬盘上。自带两个表空间：pg_default,pg_globalpg_default 用来存储系统目录对象，用户表、用户表index、临时表、临时表index、内部临时表的默认空间。
【数据库学习】Postgres(PG)原理及底层实现
午后阳光
03-23 8042
事务（transaction）：是用户定义的一组数据库操作，要么全做要么全不做，失败即回滚。保存点（savePoint）在一个大的事务中，可以把操作过程分成几个部分，第一个部分执行成功后可以建一个保存点，若后面的部分执行失败，则回滚到此保存点，而不必回滚整个事务。事务的实现即：RDBMS采取何种技术确保事务的ACID特性？回退（rollback）：撤销sql执行过程。事务管理可以管理insert、update、delete语句；不能回退create、drop操作。
except在mysql中怎么实现_有关PGSQL的INTERSECT和EXCEPT以及在MYSQL中的实现代码
weixin_39712705的博客
02-05 365
有关PGSQL的INTERSECT和EXCEPT以及在MYSQL中的实现。一下是表语句。create table t1 (id serial not null,game_id int not null);insert into t1(game_id) values (1),(2),(3),(5),(6),(8);create table t2 (id serial not null,game_id...
postgresql----UNION&&INTERSECT&&EXCEPT
weixin_33728708的博客
06-27 179
多个SELECT语句可以使用UNION，INTERSECT和EXCEPT进行集合处理，其中UNION用于求并集，INTERSECT用于求交集，EXCEPT用于求差集。用法如下 query1 UNION query2 query1 INTERSECT query2 query1 EXCEPT query2 其中query1和query2的SELECT子句中字段个数必须相同，且对应的数...
pgsql:关联查询union(并集)、except(差集)、intersect(交集)
奋斗鱼
09-28 8305
联合查询的规则是： 字段的个数和顺序必须相同 查询中的相应字段必须具有兼容的数据类型 要对合并后的结果集进行排序，可以在最后一个查询后面加上 order by 只在最后一个查询后面加，不是每个查询都加。 1.union union会移除所有重复的行，要保留重复的行，需要使用 union all。 -- 有语文成绩或数学成绩的学生 select stu_name from exam_score where subject in('语文') union select stu_name from e
【PostgreSQL】数据查询-组合查询（UNION,INTERSECT,EXCEPT）
圈小圈DBA的博客
01-13 1325
UNION有效地将 query2 的结果追加到 query1 的结果（尽管不能保证这是实际返回行的顺序）。此外，它从其结果中消除重复的行，其方式与DISTINCT 相同，除非使用UNION ALL。如果查询需要使用以下各节中讨论的任何子句，例如 .如果没有括号，您将收到语法错误，否则该子句将被理解为应用于集合操作的输出，而不是其输入之一。同样，除非使用，否则将消除重复项。为了计算两个查询的并集、交集或差值，这两个查询必须“并集兼容”，这意味着它们返回相同数量的列，并且相应的列具有兼容的数据类型。
PG in & not in系列方案比较
weixin_42767321的博客
01-02 1932
PG in&not in in VS join VS any VS exists 模型A postgres=# create table tbl_a (a integer primary key, b char(128)); CREATE TABLE Time: 67.026 ms postgres=# create table tbl_b (a integer pri...
postgresql的except关键词去重问题
leanaoo的博客
08-19 2575
今天测试项目的sql语句中有左排除，而postgresql的左排除就是左连接排除交集，用测试数据测试后发现得出的结果不正确，在比对之后才发现，except将结果按照所有列自动去重了（这也是我为了省事把唯一主键取消了，测试数据直接copy了很多条，导致找了半天错误），不过也知道了postgersql和greenplum的一个特性，except会自动去重。 ...
pgsql子查询表达式
一名普通码农的菜地
06-06 3305
PostgreSQL 8.1 中文文档 Prev Fast Backward Chapter 9. 函数和操作符 Fast Forward Next 9.16. 子查询表达式 本节描述 PostgreSQL 里面能用的 SQL 兼容的子查询表达式。所有本节中成文的表达式都返回布尔值（真/假）结果。 9.16.1. EXISTS EXIS
python 连接pg数据库
最新发布
04-03
### 使用 Python 连接 PostgreSQL 数据库 Python 提供了多种方式来连接 PostgreSQL 数据库，其中最常用的库之一是 `psycopg2`。以下是详细的说明以及示例代码。 #### 1. 安装 psycopg2 库 要使用 `psycopg2` 来连接 PostgreSQL 数据库，首先需要安装该库。可以通过以下命令完成安装： ```bash pip install psycopg2 ``` 如果遇到安装问题或者希望减少二进制依赖项，则可以考虑安装轻量版本的 `psycopg2-binary`[^2]: ```bash pip install psycopg2-binary ``` #### 2. 示例代码：连接并操作 PostgreSQL 数据库 下面是一个完整的示例代码，展示如何通过 `psycopg2` 连接到 PostgreSQL 并执行简单的 SQL 查询： ```python import psycopg2 try: # 建立与 PostgreSQL 的连接 conn = psycopg2.connect( host="localhost", database="your_database", user="your_username", password="your_password", port="5432" ) # 创建游标对象 cur = conn.cursor() # 执行 SQL 查询语句 cur.execute("SELECT * FROM your_table") # 获取查询结果 results = cur.fetchall() for row in results: print(row) except Exception as e: print(f"Error: {e}") finally: # 确保关闭游标和连接 if cur is not None: cur.close() if conn is not None: conn.close() ``` 上述代码展示了如何建立到 PostgreSQL 数据库的连接、执行查询并将结果显示出来。 #### 3. 配置文件方法 (推荐) 对于更复杂的项目，建议将数据库连接参数存储在一个单独的配置文件中，以便于管理和维护。例如，可以在项目的根目录下创建一个名为 `dbconfig.ini` 的文件，并按照如下格式填写内容[^3]： ```ini [postgresql] host = 127.0.0.1 port = 5432 database = HR user = navicat password = testnavicat ``` 接着修改之前的脚本以读取这些配置值： ```python from configparser import ConfigParser import psycopg2 def config(filename='dbconfig.ini', section='postgresql'): parser = ConfigParser() parser.read(filename) db_config = {} if parser.has_section(section): params = parser.items(section) for param in params: db_config[param[0]] = param[1] else: raise Exception(f'Section {section} not found in the {filename} file') return db_config try: # 加载配置 params = config() # 建立连接 conn = psycopg2.connect(**params) cur = conn.cursor() # 执行查询 cur.execute("SELECT * FROM employees LIMIT 5;") rows = cur.fetchall() for row in rows: print(row) except Exception as e: print(f"Database connection failed due to {e}") finally: if cur is not None: cur.close() if conn is not None: conn.close() ``` 这种方法不仅提高了可移植性和安全性，还便于团队协作时共享相同的设置。 #### 4. 替代方案：pg8000 除了 `psycopg2` 外，还可以选择其他驱动程序如 `pg8000`，这是一个纯 Python 实现的 PostgreSQL 接口工具，适合那些不希望通过 C 编译器构建环境的应用场景[^4]。其基本用法类似于 `psycopg2`: ```python import pg8000 conn = pg8000.connect( host="localhost", database="test_db", user="admin", password="secret", port=5432 ) cur = conn.cursor() cur.execute("SELECT version();") print(cur.fetchone()) cur.close() conn.close() ``` 以上即为几种常见的 Python 连接 PostgreSQL 方法及其对应实例。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值