如何在 access 中编写完整的外连接查询 [英] 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 兼容的查询之前,我还没有处理过完整"条件?
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
,或者如果没有相应的记录,则添加一些其他字段为空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.
这篇关于如何在 access 中编写完整的外连接查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!