How do I write a full outer join query in access

Question

Original query:

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

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

I am assuming:

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

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

Answer 1

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 

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.

EDIT:

See a similar approach here.

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 

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

Answer 2

The more eficient and faster code:

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