mysql select not in list

Posted in :

2023-10-27

在 MSSQL 的世界, 直接用 in 或 not in () 很方便, 但 mysql 沒有這個功能, 替代方案是把 2個 table left join 起來, 取出 null 的就是 not in.

解法:

If you don’t want to (explicitly) use temporary tables, this will work:

SELECT tmp_table.id
FROM tmp_table
LEFT JOIN users u ON tmp_table.id = u.id
WHERE u.id IS NULL;

Demo

Create the temporary table:

CREATE TABLE a_table (id INT);
CREATE TABLE b_table (id INT);

Insert values:

INSERT INTO a_table (id) values (1),(2),(3),(4),(5);
INSERT INTO b_table (id) values (1),(3),(5);

Check A not in B, should select 2,4:

select a_table.id
from a_table
left join b_table on a_table.id = b_table.id
where b_table.id is null;

相直接刪掉這些不同步的資料, 在 MS SQL 很簡單, 但在 MYSQL 很麻煩，下面的語法會顯示錯誤:

delete from a_table
left join b_table on a_table.id = b_table.id
where b_table.id is null;

修改成這樣，也是錯：

DELETE a_table.*
FROM a_table
WHERE a_table.id IN (SELECT id
             FROM (SELECT a_table.id from a_table
left join b_table on a_table.id = b_table.id
where b_table.id is null));

但是，非常神奇的是，加了一個 x 一切就OK了, 什麼鬼, 太難理解!

DELETE a_table.*
FROM a_table
WHERE a_table.id IN (SELECT id
             FROM (SELECT a_table.id from a_table
left join b_table on a_table.id = b_table.id
where b_table.id is null) x);