MySQL:仅显示两个表中不存在id的行的单个查询

Question

给定以下两个表，我如何编写一个查询来仅显示具有两个表中都不存在的 id 的行？

table_one--------------id    | name4     | Jack5     | John6     | Henry8     | Frank9     | Bob

table_two--------------id    | name4     | Jack6     | Henry8     | Frank

运行查询后的输出:John Bob

Answer 1

假设您的意思是“存在于一个表或另一个表中，但不存在于两个表中”- 您使用子查询和联合选择!

    SELECT name FROM table_one WHERE id NOT IN (SELECT id FROM table_two) 
UNION SELECT name from table_two WHERE id NOT IN (SELECT id FROM table_one);

此外，如果您希望为您将这些结果分组到一个结果行中:

SELECT group_concat(x.name) FROM 
  (SELECT name FROM table_one 
      WHERE id NOT IN (SELECT id FROM table_two) 
   UNION SELECT name from table_two 
       WHERE id NOT IN (SELECT id FROM table_one)) as x;