sql - Postgresql：如何删除 table_1 中与 table_2 不匹配的条目

Question

假设我有两张桌子：

"table_1"有columns "user_id","username","age" 和 "table_2"有columns "m_id","username","message"

现在有些用户不存在了，所以我想删除每个"table_2"包含 a 的"username"条目"table_1"

以下命令在 POSTGRESQL 中有效吗？

DELETE FROM table_2 WHERE username NOT IN 
(SELECT username FROM table_1 WHERE username IS NOT NULL)

我需要WHERE username IS NOT NULL在命令末尾吗？

以下命令起到了作用：

DELETE FROM table_2 t2 
WHERE NOT EXISTS 
(SELECT 1 FROM table_1 t1 
 WHERE t1.username = t2.username);

标签： sqlpostgresql

Answer 1

如果您担心NULL值，请使用EXISTS而不是IN：

DELETE FROM table_2 t2 
WHERE NOT EXISTS 
(SELECT 1 FROM table_1 t1 
 WHERE t1.username = t2.username);