替换从左外连接返回的默认空值

I have a Microsoft SQL Server 2008 query that returns data from three tables using a left outer join. Many times, there is no data in the second and third tables and so I get a null which I think is the default for left outer join. Is there a way to replace the default values in the select statement? I have a workaround in that I can select into a table variable but it feels a little dirty.

我有一个Microsoft SQL Server 2008查询，它使用左外部连接从三个表返回数据。很多时候，第二个和第三个表中没有数据，所以我得到一个null，我认为这是左外连接的默认值。是否有办法在select语句中替换默认值?我有一个变通方案，我可以选择一个表变量，但是感觉有点脏。

SELECT iar.Description, iai.Quantity, iai.Quantity * rpl.RegularPrice as 'Retail', 
iar.Compliance FROM InventoryAdjustmentReason iar
LEFT OUTER JOIN InventoryAdjustmentItem iai  on (iar.Id = iai.InventoryAdjustmentReasonId)
LEFT OUTER JOIN Item i on (i.Id = iai.ItemId)
LEFT OUTER JOIN ReportPriceLookup rpl on (rpl.SkuNumber = i.SkuNo)
WHERE iar.StoreUse = 'yes'

I would like the Quantity and RegularPrice to default to zero if possible.

如果可能的话，我希望数量和规则价格可以默认为零。

2 个解决方案

#1

108

That's as easy as

这是一样容易

IsNull(FieldName, 0)

Or more completely:

完全或更多:

SELECT iar.Description, 
  ISNULL(iai.Quantity,0) as Quantity, 
  ISNULL(iai.Quantity * rpl.RegularPrice,0) as 'Retail', 
  iar.Compliance 
FROM InventoryAdjustmentReason iar
LEFT OUTER JOIN InventoryAdjustmentItem iai  on (iar.Id = iai.InventoryAdjustmentReasonId)
LEFT OUTER JOIN Item i on (i.Id = iai.ItemId)
LEFT OUTER JOIN ReportPriceLookup rpl on (rpl.SkuNumber = i.SkuNo)
WHERE iar.StoreUse = 'yes'

#2

Not his ISNULL, in this IFNULL.

在这个IFNULL中，不是他的ISNULL。

 SELECT iar.Description, 
      IFNULL(iai.Quantity,0) as Quantity, 
      IFNULL(iai.Quantity * rpl.RegularPrice,0) as 'Retail', 
      iar.Compliance 
    FROM InventoryAdjustmentReason iar
    LEFT OUTER JOIN InventoryAdjustmentItem iai  on (iar.Id = iai.InventoryAdjustmentReasonId)
    LEFT OUTER JOIN Item i on (i.Id = iai.ItemId)
    LEFT OUTER JOIN ReportPriceLookup rpl on (rpl.SkuNumber = i.SkuNo)
WHERE iar.StoreUse = 'yes'

#1

108