如何连接含NULL值的两张表并获取全量匹配结果？

方案1：用全外连接（支持的数据库：PostgreSQL、SQL Server、Oracle等） #

直接通过FULL OUTER JOIN关联两张表，它会保留两张表中所有的doc_id，缺失的字段自动填充NULL：

SELECT
    COALESCE(d.doc_id, dev.doc_id) AS doc_id,
    d.id_key,
    d.doc_key,
    dev.dev_key,
    dev.dev_manager
FROM document d
FULL OUTER JOIN device dev ON d.doc_id = dev.doc_id
ORDER BY doc_id;

COALESCE函数用来确保doc_id字段始终取非空值——当记录只在device表时，document表的doc_id会是NULL，反之同理。

方案2：兼容不支持全外连接的数据库（如MySQL） #

如果你的数据库不支持FULL OUTER JOIN，可以用LEFT JOIN加RIGHT JOIN配合UNION ALL实现：

-- 先获取document表所有记录，关联对应的device数据
SELECT
    d.doc_id,
    d.id_key,
    d.doc_key,
    dev.dev_key,
    dev.dev_manager
FROM document d
LEFT JOIN device dev ON d.doc_id = dev.doc_id

UNION ALL

-- 再获取device表中没有对应document的记录
SELECT
    dev.doc_id,
    NULL AS id_key,
    NULL AS doc_key,
    dev.dev_key,
    dev.dev_manager
FROM device dev
LEFT JOIN document d ON dev.doc_id = d.doc_id
WHERE d.doc_id IS NULL

ORDER BY doc_id;

这个方法先拿到所有document的记录（包括匹配到device的），再单独提取device中独有的记录，最后合并结果，避免重复。

执行结果 #

不管用哪种方案，最终都会得到你想要的结果：

doc_id    id_key   doc_key   dev_key    dev_manager
A         100      11111     111        John
B         200      22222     222        Smith
C         300      33333     null       null
D         null     null      444        Jane
E         500      55555     null       null
F         null     null      666        Sue

内容的提问来源于stack exchange，提问作者hannie