模拟FULL OUTER JOIN：LEFT+RIGHT JOIN的UNION与交叉连接的性能及弊端

Hey, let's break this down clearly since Access/Jet doesn't support FULL OUTER JOIN natively.

First off, if you try running this standard full outer join query in Access, it'll throw an error:

SELECT Table1.*, Table2.* FROM Table1 FULL OUTER JOIN Table2 ON Table1.JoinField = Table2.JoinField

The Go-To Alternative #

The widely recommended workaround is combining a LEFT JOIN and RIGHT JOIN with UNION ALL to replicate the full outer join behavior. Here's how that looks:

SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN Table2 ON Table1.JoinField = Table2.JoinField 
UNION ALL 
SELECT Table1.*, Table2.* FROM Table1 RIGHT JOIN Table2 ON Table1.JoinField = Table2.JoinField 
WHERE Table1.JoinField IS NULL

Using UNION ALL instead of plain UNION avoids the overhead of deduplicating records, since the RIGHT JOIN with the WHERE clause only returns records from Table2 that don't match anything in Table1—no overlap with the left join results.

Can You Use a Cross Join Instead? #

You mentioned trying this cross join approach:

SELECT Table1.*, Table2.* FROM Table1, Table2 WHERE Table1.JoinField = Table2.JoinField OR Table1.JoinField IS NULL OR Table2.JoinField IS NULL

While this might seem like it works at first glance, it has major drawbacks:

• Catastrophic Performance: A cross join generates a Cartesian product of both tables—meaning every row in Table1 pairs with every row in Table2. If you have even moderately sized tables (say 1k rows each), that's 1 million rows to process before filtering. This is way less efficient than the join-union method, which leverages join logic to only process relevant matches and unmatched records.

• Incorrect Results: If either table has rows where JoinField is NULL, the OR conditions will pair those rows with every row in the other table. For example, a single NULL in Table1's JoinField would create N duplicate rows (one for each row in Table2), which isn't what a full outer join does—full outer joins only keep unmatched rows with NULLs from the other table, not all possible combinations.

• Index Inefficiency: Joins on JoinField can use indexes to speed up matching, but the OR conditions in the cross join's WHERE clause make it hard for the database to utilize indexes effectively, worsening performance even more.

So in short: stick with the LEFT/RIGHT JOIN + UNION ALL approach. The cross join method is unreliable and will cause performance headaches, especially as your tables grow.

内容的提问来源于stack exchange，提问作者user20416