您正在寻找的是完全外联接,但MySQL是极少数不支持完全外联接的RDBMS之一.
完整的外部联接如下所示:
SELECT
COALESCE(t1.id, t2.id) AS id,
(t1.id IS NOT NULL AND t2.id IS NOT NULL) AS flag
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t2.id = t1.id
ORDER BY COALESCE(t1.id, t2.id);
以下是两种替代方法:
收集所有ID,然后判断它们是否存在于两个表中:
SELECT
id
id IN (SELECT id FROM table1)
AND
id IN (SELECT id FROM table2) AS flag
FROM
(
SELECT id FROM table1
UNION
SELECT id FROM table2
) ids
ORDER BY id;
Select 匹配项、缺少的Table2 ID和缺少的Table1 ID,然后合并这些结果.
SELECT id, true AS flag FROM table1 JOIN table2 USING (id)
UNION ALL
SELECT id, false AS flag FROM table1 WHERE id NOT IN (SELECT id FROM table2)
UNION ALL
SELECT id, false AS flag FROM table2 WHERE id NOT IN (SELECT id FROM table1)
ORDER BY id;
UPDATE:我刚看到你给MySQL和Amazon Athena都加了标签.这是两个不同的数据库管理系统.MySQL不支持完全外连接,而Athena支持.因此,对于Athena来说,所有三个查询都应该可以,而对于MySQL,只有第二个和第三个查询可以.