SQL NOT IN 优化语句，存储过程执行查询快到心跳加速！

最新推荐文章于 2024-08-07 15:58:20 发布

骚团长

最新推荐文章于 2024-08-07 15:58:20 发布

阅读量724

点赞数

分类专栏：小小笔录

本文链接：https://blog.csdn.net/u010944388/article/details/53635360

版权

小小笔录专栏收录该内容

12 篇文章 2 订阅

订阅专栏

NOT IN SQL 语句如下：
当存储过程执行not in 大量数据的时候，响应需要1分钟以上！
SELECT *FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120082'
AND partno NOT IN 
( SELECT partno FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120142')		
/*******************************************简化表参数易学易懂！
SELECT *FROM table (NOLOCK) WHERE id='63514-16120082'
AND data NOT IN 
( SELECT id FROM table (NOLOCK) WHERE id='63514-16120142')		
 *******************************************/

LEFT JOIN SQL 语句如下：
速度大大提升，用 NOT IN  响应42秒的语句而用LEFT JOIN只需要用4秒
SELECT * FROM 
(SELECT *FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120082')AS a
LEFT JOIN 
( SELECT * FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120142')AS b
ON a.partno=b.partno
where b.partno IS NULL
/*******************************************简化表参数易学易懂！
SELECT * FROM 
(SELECT *FROM table (NOLOCK) WHERE id='63514-16120082')AS a
LEFT JOIN 
( SELECT * FROM table (NOLOCK) WHERE id='63514-16120142')AS b
ON a.data=b.data
where b.data IS NULL
 *******************************************/

NOT EXISTS SQL 语句如下：
速度超级大大提升，用 NOT IN  响应42秒的语句而用LEFT JOIN只需要用1-2秒
SELECT * FROM 
(SELECT *FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120082') AS a 
where NOT EXISTS (
SELECT *FROM 
( SELECT partno FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120142') AS b WHERE a.partno=b.partno)

/*******************************************简化表参数易学易懂！
SELECT * FROM 
(SELECT *FROM table (NOLOCK) WHERE id='63514-16120082') AS a 
where NOT EXISTS (
SELECT *FROM 
( SELECT data FROM table (NOLOCK) WHERE id='63514-16120142') AS b WHERE a.data=b.data)
 *******************************************/