删除不在vs中的差异不存在

我有两个场景,分别是场景1和场景2,但这两个场景都实现了相同的目标, 注意两种情况 otherTbl 是静态的

场景1

CREATE TABLE `tbl`(
    col1 VARCHAR(255),

    PRIMARY KEY(col1)
) ENGINE='InnoDb';

这是我以前运行的一组查询,它们很有意义并且运行良好。

#Create an exact copy of the `tbl`
CREATE TEMPORARY TABLE `tmp_tbl`( .. SAME AS `tbl` .. );

#Add grouped records from another table into `tmp_table`
INSERT INTO tmp_tbl SELECT col1 FROM otherTbl GROUP BY col1;

#Delete the tables that donot exist any more int the `otherTbl`
DELETE FROM tbl WHERE tbl.col1 NOT IN (SELECT col1 FROM tmp_tbl);

场景2

在这个场景中,区别仅仅在于列,正如您可以看到的,所有列都是主键。

CREATE TABLE `tbl`(
    col1 VARCHAR(255),
    col2 VARCHAR(255),
    col3 VARCHAR(255),

    PRIMARY KEY(col1, col2, col3)
) ENGINE='InnoDb';

这是一组新的查询

#Create an exact copy of the `tbl`
CREATE TEMPORARY TABLE `tmp_tbl`( .. SAME AS `tbl` .. );

#Add grouped records from another table into `tmp_table`
INSERT INTO tmp_tbl 
    SELECT col1, col2, col3 FROM otherTbl GROUP BY col1, col2, col3;

#Delete the tables that donot exist any more int the `otherTbl`
DELETE FROM tbl WHERE NOT EXISTS(SELECT col1, col2, col3 FROM `tmp_tbl`);

问题很简单,如果我们将删除查询替换为 NOT IN 到 NOT EXISTS 在场景1中,它仍然以相同的方式工作。

******简单版本******

是:

DELETE FROM `tbl` WHERE tbl.col1 NOT IN (SELECT col1 FROM tmp_tbl);

等于:

DELETE FROM `tbl` WHERE NOT EXISTS(SELECT col1 FROM `tmp_tbl`);