mysql select not in list

在 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);

最後, 刪掉測試用 table:

drop table a_table;
drop table b_table;

附註: 在測試沒驗證過的 sql command 前, 請先備份 database, 以免重要資料在非預期的狀態下被 delete command 刪掉…。

---

資料來源

SELECT that returns list of values not occurring in any row
https://stackoverflow.com/questions/10013475/select-that-returns-list-of-values-not-occurring-in-any-row

Query to Check for Values Not Matching List in Where Clause
https://stackoverflow.com/questions/72218215/query-to-check-for-values-not-matching-list-in-where-clause