联接查询中出现意外的空值

Question

我在查询中使用了 3 个表：2 个带有INNER JOIN个表，第三个表带有LEFT JOIN个表。

但是，在执行LEFT JOIN条件时，我得到了一些NULL记录。有来自采购行(PL_表('不应该是这种情况。

任何人都可以建议更改查询以将记录中的NULL值替换为实际值吗？

抱歉，我找不到任何附加示例表数据的选项。

查询

SELECT
pih.[PO Number], 
pih.[Pre-Assigned No_] as [Invoice No],
pil.[Document No_],
pil.[Description] as [Reason For Discrepency],
pil.[Line No_],
pl.[No_] as [Item No],
pl.[Vendor Item No_],
pl.[Order Date],
pil.[Posting Date],
pil.[Expected Receipt Date],
pih.[Notes] as [Header Notes],
pil.[No_] as [G/L Account No],
pih.[Buy-from Vendor No_],
pih.[Buy-from Vendor Name],
Pil.Quantity as [Inv Qty From InvoiceLine], 
pil.[Amount Including VAT] as [Inv Value From InvoiceLine], 
pl.Quantity as [PO Quantity From Purchaseline], 
pl.[Quantity Received] as [Received Qty From PurchaseLine],
pl.[Quantity Invoiced] as [Invoiced Qty From PurchaseLine] ,
pl.[Amount Including VAT] as [PO Value From PurchaseLine]
FROM 
[Purch_ Inv_ Line] pil
INNER JOIN Purch_ Inv_ Header] pih
ON pil.[Document No_] = pih.[No_]
LEFT JOIN [Purchase Line] pl 
ON pih.[PO Number]=pl.[Document No_] and pl.[Line No_]=pil.[Line No_]
WHERE 
PIL.[Document No_] IN
(
SELECT distinct pil.[Document No_] FROM   
Purch_ Inv_ Line] pil 
WHERE piL.[No_] in ('700xxx','700xxx','17xxxxx') and pil.[Posting Date] >=getdate()-7
)
AND piL.[Type]='1'

Answer 1

获得NULL值的原因可能有 2 个：

• [采购行]表中的列值实际上是NULL

• 对于NULL的行，[采购行]表中没有匹配的行。这就是左联的工作方式

如果只想获取匹配的行，请将LEFT JOIN更改为INNER JOIN- 但是，结果集将不包含 [Purch_ Inv_ 行] 和[Purch_ Inv_ 标题] 中没有匹配的行 [购买行] 行。

如果要为NULL值提供默认值，可以使用ISNULL

：

ISNULL(pl.[Amount Including VAT], 0) as [PO Value From PurchaseLine]