Access SQL查询:左联接不显示所有左表行，这是一个Union查询

Question

我有两个查询和一个想要合并的表：

• Product Demand
• NA Supply
• EU Supply

这三个领域具有相同的字段：

• 日期
• 地域
• 产品
• 数量

虽然，我要说，在供应查询中，我强制Date和Region是静态值，而Product和Quantity是从表中提取出来的。例如：

SELECT 
  DateValue("12/1/2017") AS [Date], 
  "North America" AS [Region],  <-- Would be "Europe" for [EU Supply]
  [NA Inv Report].[Product], 
  [NA Inv Report].[Quantity]
FROM [NA Inv Report]   <-- Would be "EU Inv Report" for [EU Supply]
  WHERE ((([NA Inv Report].[Quantity])>0));

然后我有一个基本的联合查询，名为[uQuery: Combo]

SELECT 
  [Product Demand].[Date] AS [Date],
  [Product Demand].[Region] AS [Region],
  [Product Demand].[Product] AS [Product]
FROM [Product Demand]
UNION SELECT 
  [NA Supply].[Date] AS [Date],
  [NA Supply].[Region] AS [Region],
  [NA Supply].[Product] AS [Product]
FROM [NA Supply]
UNION SELECT 
  [EU Supply].[Date] AS [Date],
  [EU Supply].[Region] AS [Region],
  [EU Supply].[Product] AS [Product]
FROM [EU Supply];

然后，我将Union查询拉到一个新的查询中；

SELECT 
  [uQuery: Combo].Date, 
  [uQuery: Combo].Region, 
  [uQuery: Combo].Product
FROM [uQuery: Combo];

，这显示了一切！

但让人困惑的是，如果我离开了“加入Product Demand”，它仍然显示了一切。但是，如果我离开了NA Supply或EU Supply中的一个，那么它将把结果过滤到添加的表中。如果我同时添加两个供应表，那么它将不会显示任何结果。

SELECT 
  [uQuery: Combo].Date, 
  [uQuery: Combo].Region, 
  [uQuery: Combo].Product
FROM (([uQuery: Combo] 
  LEFT JOIN [Product Demand] ON  <-- Adding just this will show everything
    ([uQuery: Combo].Product = [Product Demand].Product) AND 
    ([uQuery: Combo].Region = [Product Demand].Region) AND 
    ([uQuery: Combo].Date = [Product Demand].Date)) 
  LEFT JOIN [NA Supply] ON  <-- Adding this will only show matching results for [NA Supply]
    ([uQuery: Combo].Product = [NA Supply].Product) AND 
    ([uQuery: Combo].Region = [NA Supply].Region) AND 
    ([uQuery: Combo].Date = [NA Supply].Date)) 
  LEFT JOIN [EU Supply] ON  <-- Adding this will hide all results
    ([uQuery: Combo].Product = [EU Supply].Product) AND 
    ([uQuery: Combo].Region = [EU Supply].Region) AND 
    ([uQuery: Combo].Date = [EU Supply].Date);

Answer 1

所有的结果都会消失，因为你在强迫NA和EU区域匹配，它们永远不会匹配，因为你硬编码它们是不同的值。即使使用LEFT JOIN，NA供应也不会以逻辑"North America" <> "Europe"的形式返回任何行。

考虑分离供求数据，然后加入最终的查询，其中匹配是潜在的需求和供应区域。

联合查询

SELECT 
  [NA Supply].[Date] AS [Date],
  [NA Supply].[Region] AS [Region],
  [NA Supply].[Product] AS [Product]
FROM [NA Supply]
UNION 
SELECT 
  [EU Supply].[Date] AS [Date],
  [EU Supply].[Region] AS [Region],
  [EU Supply].[Product] AS [Product]
FROM [EU Supply]

最终查询

SELECT 
  u.[Date], 
  u.Region, 
  u.Product,
  u.Quantity AS SupplyQty,
  p.Quantity AS DemandQty
FROM [uQuery: Combo] u
LEFT JOIN [Product Demand] p ON
  u.[Date] = p.[Date] AND 
  u.Region = p.Region AND 
  u.Product = p.Product

完全外联查询

现在，您可能需要一个完整的外部连接，以包括需求，而不匹配供应。而且，由于MS Access中不直接支持FULL OUTER JOIN，所以可以通过合并一个RIGHT JOIN (假设两个表中不存在重复行)实现等效的结果。

SELECT u.[Date], u.Region, u.Product, u.Quantity As SupplyQty, p.Quantity As DemandQty
FROM [uQuery: Combo] u
LEFT JOIN [Product Demand] p ON
  u.[Date] = p.[Date] AND u.Region = p.Region AND u.Product = p.Product

UNION

SELECT u.[Date], u.Region, u.Product, u.Quantity, p.Quantity
FROM [uQuery: Combo] u
RIGHT JOIN [Product Demand] p ON
  u.[Date] = p.[Date] AND u.Region = p.Region AND u.Product = p.Product

旁白:理想情况下，您希望维护一个供应源或一个Inv报告，因为UNION可能存在性能问题，特别是在其他使用UNION的完整外部连接等查询中。

在数据库架构中，您不希望为不同类别(如NA或EU )复制结构，但在相同的对象中保留相同的数据，并在数据值不同的指示字段中保留类似的数据。数据库表不是电子表格选项卡，而是标准化的、相关的避免冗余的逻辑数据分组。