如果一行在两个表中都没有出现，则将其删除(删除孤立行)

Question

如果在banners_pp中没有相应的行，我想删除banners中的any行

我该如何处理{doesn't exist}部件？

DELETE banners.* FROM banners
LEFT JOIN banners_pp
USING(banner_id)
WHERE banners_pp.banner_id {doesn't exist}

Answer 1

DELETE FROM banners
WHERE NOT EXISTS (
    SELECT *
    FROM banners_pp
    WHERE banner_id  = banners.banner_id 
)

查看more详细信息

Answer 2

使用WHERE banners_pp.banner_id IS NULL

在LEFT JOIN中，左表(banners)中的行在右表(banners_pp)中没有相应的行，但在右表的列中有NULL。如果在banners_pp中没有包含banner_id = 2的条目，但在banners中存在包含banner_id = 2的条目，则连接表中的相应行可能如下所示：

 | banners.banner_id | banners.foo | banners_pp.banner_id | banners_pp.bar |
 |         2         |     x       |         NULL         |        NULL    |

Answer 3

DELETE 
 FROM banners
 WHERE NOT EXISTS (SElect 1 from banners_pp
          WHERE banners_pp.banner_id = banners.banner_id)