"或者"加入选项 [英] "either" join option

问题描述

Access中通常有3个连接选项：


1.必须在两个表中找到记录

2.必须在表格中找到记录; x，但不是表格y

3.记录必须在表格y中找到，但不能在表格x中找到


是否存在包含两个表中所有唯一记录的连接选项？

There are normally 3 join options in Access:

1. Records must be found in both tables
2. Records must be found in table "x", but not table "y"
3. Records must be found in table "y", but not table "x"

Is there a join option that includes all unique records from both tables?

推荐答案

使用INNER JOIN，LEFT JOIN和RIGHT JOIN查询并将它们与UNION组合。


访问没有OUTER JOIN :-（


Nic; o）

Use a INNER JOIN, LEFT JOIN and RIGHT JOIN query and combine them with a UNION.

Access has no OUTER JOIN :-(

Nic;o)

看看有关详情，请访问 SQL JOIN 。

访问不支持FULL OUTER JOIN，这是你所指的，虽然其他数据库引擎也是如此。


Nico的想法，重叠多个连接，几乎可以在所有情况下工作。它不需要INNER JOIN，因为它是LEFT JOIN和RIGHT JOIN的子集。那就是说，它应该为你做好。我提到几乎所有情况，因为[表A]和[表B]匹配时存在重叠。为避免重复记录，在这种情况下，必须使用UNION版本，而不是UNION ALL版本。在大多数情况下，这很好，但是可能存在有效复制的数据集，这会影响（通过删除重复 - 甚至是有效的重复）。

Have a look at SQL JOINs for more on this.

Access doesn''t support the FULL OUTER JOIN, which is what you''re referring to, though other database engines do.

Nico''s idea, to overlap multiple joins, can work in almost all cases. It wouldn''t need the INNER JOIN, as this is a subset of both the LEFT JOIN and the RIGHT JOIN. That said, it should do the job for you. I mentioned almost all cases, as there is overlap where [Table A] and [Table B] match. To avoid duplicating the records it is necessary to use the UNION version, as opposed to the UNION ALL version, in a case like this. In most cases this is fine, but there can be data sets where there are duplicates occuring validly, which this would effect (by removing the duplicates - even the valid ones).

糟糕，好评论NeoPa。期待在指示源（内部/左/右）的查询中添加额外的TypeJoin变量。然而，这将需要LEFT和RIGHT JOIN来测试相反中的缺失键。表。


Nic; o）

Oops, good remark NeoPa. Was anticipating to add an additonal TypeJoin variable in the queries indicating the source (Inner/Left/Right). This would however require the LEFT and RIGHT JOIN to test for a missing key in the "opposite" table.

Nic;o)

这篇关于"或者"加入选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！