如何在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屋！