那很容易

IsNull(FieldName, 0)

或者更全面地说:

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'

万一 MySQL SQLite 正确的关键字是 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'

MySQL

COALESCE(field, 'default')

例如:

  SELECT
    t.id,
    COALESCE(d.field, 'default')
  FROM
     test t
  LEFT JOIN
     detail d ON t.id = d.item

NULL 通过 COALESCE 功能。 例如:

mysql> SELECT COALESCE(NULL, 1, NULL);
        -> 1
mysql> SELECT COALESCE(0, 1, NULL);
        -> 0
mysql> SELECT COALESCE(NULL, NULL, NULL);
        -> NULL

为了

NVL(columnName,deafultValue) :-NVL用于将空值转换为查询输出中的默认值。如果你想用“NA”来替换空值,那么使用类似这样的东西。

SELECT NVL(columnName,'NA') FROM tableName