sql语句之IN&&EXISTS

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

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

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