NOT IN 方法改进

今天在写一条sql查询语句,其需要从一个表A中返回所有A不再表B中的结果集,当然,这种实现最方便的方法就是用NOT IN

如:select a.* from a where a.id not in (select id from b where…..)

我们大家都知道很多sql方面的文章都建议大家尽量不要使用NOT IN的方法,因为这种方法的效率不高。那有没有替代的办法呢?(声明因为当时的情况要求不能使用储存过程,所以只有写sql语句)和同事实验了一下,结果用以下方法实现了。

 

目的:

替换NOT IN 方法。

说明:

在单条SQL语句中,不使用储存过程,不使用临时表。使用存储过程和临时表不再本文的讨论范围中。

实现:

例:

aa:结构

id                                       value   ……

1                                        a

2                                            b

3                                            c

4                                            d

5                                            e

6                                            f

---------------------------------------------------------------------------------------------

 

bb:结构

id    ……

2

4

6

 

现在我要取表aa里的所有字段,条件是aaid值不在bbid值当中(not in)。也就是应该返回所有id为奇数的字段

 

使用NOT INSQL:

select * from aa where id not in(select id from bb)

就一条语句,简单明了,可惜效率不高,而且公司规范要求尽量不用NOT IN,害我费了好大事crying……

 

改造后的SQL:

select cc.id,cc.value from (select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id) as cc where cc.tempcolum is null

 

解释一下。在开始时候我最早想用内联表的方式,可是无论如何也每找到一个好的办法,干脆就是实现不了。(大家有好办法指教先。)

后来自己考虑了一下NOT IN的逻辑,A NOT IN B就是说A是主体,B起到的之不过是一个判断作用,我们可以先把所有符合条件的A记录全部查询出来而不管他是否属于B,然后再从这里剔除值同时属于B的部分。

Select aa.* from aa

但是仅仅这样是不够的,我们无法利用这个返回的结果集判断是否属于B并排除它,为此,我想到构造一个临时的列,这个列的值应该是在A的结果集范围内,所有在B中的值。而这个结果集的主体应该是所有满足先决条件的A,然后加上满足条件的B,而不满足条件的B值则不再考虑范围内,所以用了left join

这一段是关键,不知道我阐述清楚了没有,没明白的继续看

于是就出来这一句。

select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id

没看明白上面的看结果集就明白了

id  value        tempcolum

---------------------------------------------

1     a                NULL

2     b                2

3     c                NULL

4     d                4

5     e                NULL

6     f                6

 

看到这个结果集我想大家都明白我的意思了吧。对了,我们就是要对这个结果集进行二次操作。

相信大家都看到了,生成的这个结果集包含了所有符合条件的表aa字段和bbid,如果aa中的值在bb中,则tempcolum的值就不会为null,如果不在就是null,这样我们只需要从这个结果集里查询所有tempcolum值为null的就可以满足我们的要求了

所以最终的sql出来了

select cc.id,cc.value from (select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id) as cc where cc.tempcolum is null

我们只需要idvalue两个字段,其他的就不要了。

结果

id  value

-------------------------

1     a        

3     c        

5           e        

 

ok,实现了,希望对大家有帮助。

<think>我们正在处理用户关于SQL查询优化的请求:用户希望将'NOT IN'子句改写为'NOT EXISTS'结构,以提高性能和正确处理NULL值。 根据引用[1]提到:对于使用子查询来修改单个表的UPDATE和DELETE语句,优化器不会使用半连接(semijoin)或物化子查询优化。建议将它们重写为使用连接(join)而不是子查询的多表UPDATE和DELETE语句。 虽然这里用户的问题是关于SELECT查询(用户没有明确说明,但通常NOT IN/NOT EXISTS用于SELECT),但我们可以借鉴这个思想:在某些情况下,改写为连接或使用EXISTS可能更高效。 特别要注意:NOT IN在子查询结果包含NULL值时会导致整个条件返回NULL(即没有行满足),而NOT EXISTS则没有这个问题。 因此,我们将展示如何将NOT IN改写为NOT EXISTS,并解释为什么这样改写。 步骤: 1. 给出一个典型的NOT IN查询示例。 2. 将其改写为NOT EXISTS。 3. 解释性能差异和NULL值处理。 示例场景:假设我们有两个表:`customers`(客户)和 `orders`(订单)。我们想找出没有下过订单的客户。 使用NOT IN的写法: ```sql SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders); ``` 问题:如果子查询`(SELECT customer_id FROM orders)`返回的结果集中包含NULL值,那么整个NOT IN条件将返回NULL(即没有行满足条件),这通常不是我们想要的。 使用NOT EXISTS的改写: ```sql SELECT * FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id ); ``` 在NOT EXISTS中,即使子查询中有NULL值,只要没有匹配的行,条件就会满足。而且,EXISTS通常可以利用索引,性能更好。 性能说明:数据库优化器在处理NOT EXISTS时,通常能够使用半连接优化(如引用[1]提到的半连接转换),或者至少能够使用相关子查询的索引。而NOT IN则可能需要对整个子查询结果集进行物化并进行全表扫描,尤其是当子查询结果集很大时。 注意:如果子查询中的列定义为非空(例如,主键),那么NOT INNOT EXISTS在逻辑上是等价的,但性能可能仍有差异。 另外,引用[1]还提到对于单表的UPDATE/DELETE,如果使用子查询,优化器不会使用半连接优化,因此建议使用多表连接形式。对于SELECT语句,我们同样可以考虑使用LEFT JOIN + IS NULL的方式,但这里用户要求改写为NOT EXISTS。 因此,我们推荐使用NOT EXISTS来替代NOT IN,特别是在处理可能包含NULL值的情况时。 生成相关问题: 1. 除了NOT EXISTS,还有哪些方法可以优化包含NOT IN的查询? 2. 在什么情况下NOT INNOT EXISTS的性能差异最大? 3. 如何确保子查询中的列不会包含NULL值以避免NOT IN的问题?</think>### 将 `NOT IN` 改写为 `NOT EXISTS` 的 SQL 优化指南 #### 为什么需要改写? 1. **性能优化** `NOT IN` 在子查询结果较大时可能导致全表扫描,而 `NOT EXISTS` 通常能利用索引进行半连接优化,减少数据处理量[^1]。 2. **NULL 值处理** `NOT IN` 在子查询包含 NULL 时会返回空结果(逻辑失效),而 `NOT EXISTS` 能正确处理 NULL 值[^1]。 #### 改写示例 **原始 NOT IN 语句**: ```sql SELECT * FROM employees WHERE dept_id NOT IN ( SELECT dept_id FROM departments WHERE status = 'active' ); ``` **优化后的 NOT EXISTS 语句**: ```sql SELECT e.* FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM departments d WHERE d.dept_id = e.dept_id AND d.status = 'active' -- 显式关联主查询 ); ``` #### 关键改进点: 1. **关联条件** 在子查询中添加 `d.dept_id = e.dept_id` 建立显式关联,使优化器能使用索引。 2. **SELECT 1 惯例** 使用 `SELECT 1` 代替 `SELECT *` 减少数据加载。 3. **NULL 安全性** 即使 `departments.dept_id` 包含 NULL,结果仍正确。 #### 性能验证建议 ```sql EXPLAIN SELECT e.* FROM employees e WHERE NOT EXISTS (...); ``` 检查执行计划: - 确认使用索引(如 `departments.dept_id` 索引) - 避免 `Full Table Scan` 标记 #### 适用场景 ✅ 大表关联小表 ✅ 子查询结果含 NULL ✅ 需要索引优化
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值