Replace Default Null Values Returned From Left Outer Join

user_117502 0
Viewed 153840

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.

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.

sql-server-2008 tsql sql-server mysql sql
4 Answers

MySQL

COALESCE(field, 'default')

For example:

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

Also, you can use multiple columns to check their NULL by COALESCE function. For example:

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

For Oracle you can use:

NVL(columnName,deafultValue) :- NVL is used to convert a null value to a default value in the query output. eg. If you want to replace null values with 'NA' then use something like this.

SELECT NVL(columnName,'NA') FROM tableName

user_132762630
Related