如何在访问中编写完整的外部联接查询 [英] How do I write a full outer join query in access

问题描述

原始查询:

SELECT * 
FROM AA
FULL OUTERJOIN BB on (AA.C_ID = BB.C_ID);  

如何转换上面的查询以使其在 Microsoft Access 中兼容?

How do I convert the query above to make it compatible in Microsoft Access?

我假设:

SELECT *
FROM AA
FULL LEFT JOIN BB ON (AA.C_ID = BB.C_ID);

在我正确地将第一个查询转换为与 Access 兼容的查询之前，我没有处理过FULL"条件吗?

I haven't dealt with the "FULL" criteria before am I correctly converting the first query into a query compatible with Access?

推荐答案

假设 AA 和 BB 中没有重复的行(即所有相同的值)，完全外连接相当于左连接和右连接.

Assuming there are not duplicate rows in AA and BB (i.e. all the same values), a full outer join is the equivalent of the union of a left join and a right join.

SELECT *
    FROM AA
        LEFT JOIN BB ON AA.C_ID = BB.C_ID
UNION
SELECT *
    FROM AA
        RIGHT JOIN BB ON AA.C_ID = BB.C_ID

如果有重复的行(并且您想保留它们)，请在末尾添加 WHERE AA.C_ID IS NULL，或者如果没有相应的记录，则添加其他仅为 null 的字段AA.

If there are duplicate rows (and you want to keep them), add WHERE AA.C_ID IS NULL at the end, or some other field that is only null if there is not corresponding record from AA.

查看类似的方法 这里.

它建议更详细，但性能更高

It recommends the more verbose, but more performant

SELECT *
    FROM AA
        JOIN BB ON AA.C_ID = BB.C_ID
UNION ALL
SELECT *
    FROM AA
        LEFT JOIN BB ON AA.C_ID = BB.C_ID
    WHERE BB.C_ID IS NULL
UNION ALL
SELECT *
    FROM AA
        RIGHT JOIN BB ON AA.C_ID = BB.C_ID
    WHERE AA.C_ID IS NULL

但是，这假设 AA.C_ID 和 BB.C_ID 不为空.

However, this assumes that AA.C_ID and BB.C_ID are not null.

这篇关于如何在访问中编写完整的外部联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！