How to Simulate a FULL OUTER JOIN in Microsoft Access?

Writing a full outer join query in Access

Question:

You have a query in another database system that uses a full outer join. How can I convert this query to one compatible with Microsoft Access?

Problem description:

Access does not have direct FULL OUTER JOIN syntax. A full outer join returns all rows from both tables, even if there are no matching rows in the other table.

Solution:

To implement a full outer join in Access, you can use a combination of UNION and LEFT/RIGHT JOIN statements:

<code class="language-sql">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</code>

Other notes:

• If there are duplicate rows in either table, you may need to add a WHERE clause to filter out the extra rows.
• If the C_ID field in both tables may be null, you can use a more performant but more verbose approach:

<code class="language-sql">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</code>

The above is the detailed content of How to Simulate a FULL OUTER JOIN in Microsoft Access?. For more information, please follow other related articles on the PHP Chinese website!