sql语句之IN&&EXISTS

最新推荐文章于 2024-10-31 14:51:40 发布

原创最新推荐文章于 2024-10-31 14:51:40 发布 · 253 阅读

0 ·

CC 4.0 BY-SA版权

数据库之拾遗专栏收录该内容

7 篇文章

订阅专栏

本文深入探讨了SQL中NOTIN和NOTEXISTS的使用方式，通过具体实例展示了如何在etruck_gw.gis_info和request表中应用这些操作符。同时，对比了两个SQL查询在计数重复记录上的不同表现，帮助读者理解在实际场景中如何正确选择使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

NOT IN使用：DELETE FROM userwhere NAME not in (select name from usermood);

NOT EXISTS使用：DELETE FROMuser where NOT EXISTS (select from usermood where `user`.`name`=usermood.`name`);

使用实例：放在etruck_gw.gis_info表

sql_dele="delete from etruck_gw.gis_info where NOTEXISTS(select * from etruck_gw.dev whereetruck_gw.gis_info.id=etruck_gw.dev.last_gis_info_id andetruck_gw.gis_info.created_time >= '${tmpdateh0}' andetruck_gw.gis_info.created_time < '${tmpdateh1}')"

放在etruck_gw.request表

sql_dele="delete from etruck_gw.request where NOTEXISTS(select * from etruck_gw.dev,etruck_gw.gis_info whereetruck_gw.request.id=etruck_gw.dev.last_req_id oretruck_gw.request.id=etruck_gw.gis_info.request_id andetruck_gw.request.updated_time >= '${tmpdateh0}' and etruck_gw.request.updated_time< '${tmpdateh1}')"

发现问题：

SELECT
count(*)
FROM
etruck_gw.gis_info,etruck_gw.request
WHERE
etruck_gw.request.id = etruck_gw.gis_info.request_id
AND etruck_gw.request.updated_time >= '2015-11-1'
AND etruck_gw.request.updated_time < '2016-12-2';

与

SELECT
count(*)
FROM
etruck_gw.request
WHERE
EXISTS( SELECT
*
FROM
etruck_gw.gis_info
WHERE
etruck_gw.request.id = etruck_gw.gis_info.request_id)
AND etruck_gw.request.updated_time >= '2015-11-1'
AND etruck_gw.request.updated_time < '2016-12-2';

结果：97828

96757

解释：前者为等值连接，会计数重复记录

后者不记录重复记录，但做删除时结果相同。