如何在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兼容的查询之前,我还没有处理过"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
,或者如果没有来自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屋!