如何从mysql Join结果中删除匹配的行，并显示不匹配的行？

Question

有没有办法从MySQL连接查询中删除匹配的行？实际上我有两个表，在这两个表中我存储了pub_id和post_id，这两个表是通用的。当我查询来自table1和table2的所有匹配行时，我想要一个结果，不应该列出所有匹配的行，应该只列出不匹配的行。

Answer 1

查询返回仅存在于以下两个表之一中的行：

SELECT *
FROM Table1 t1
WHERE NOT EXISTS (Select 1 from Table2 t2 
                 Where t1.pub_id = t2.pub_id
                AND t1.post_Id = t2.post_id)
UNION ALL
SELECT *
FROM Table2 t1
WHERE NOT EXISTS (Select 1 from Table1 t2 
                 Where t1.pub_id = t2.pub_id
                AND t1.post_Id = t2.post_id)

Answer 2

你需要这样的东西：

    SELECT * FROM tablea AS a
   RIGHT  JOIN tableb AS o ON a.id = o.id WHERE a.pub_id IS NULL and a.post_id is null 
UNION    
SELECT * FROM tablea AS a
   LEFT  JOIN tableb AS o ON a.id = o.id WHERE o.pub_id IS NULL and o.post_id is null