Replace Default Null Values Returned From Left Outer Join

Question

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.

**Note:** Some of the answers on this post deal with databases other than MSFT sql-server, causing this answer to show up on search results pages for those other contexts as well. — dreftymac, May 16 '19 at 19:25
**See also:** https://stackoverflow.com/questions/799375/sqlite-equivalent-to-isnull-nvl-ifnull-or-coalesce — dreftymac, May 16 '19 at 19:26

score 163 · Accepted Answer · answered Nov 02 '09 at 22:56

163

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'

answered Nov 02 '09 at 22:56

Michael Haren

105,752
40
168
205

4

I knew it had to be easy but for some reason, I was blocked on how to do it. Thanks. – Brett Bim Nov 02 '09 at 22:59
53

If it is MySQL, IsNull should be replaced with 'IFNULL'. Thanks. – Dhanushka Jul 14 '14 at 06:54
19

In PostgreSQL, it seems to be ["COALESCE"](http://www.postgresql.org/docs/9.3/static/functions-conditional.html). For MySQL, the manual page is here: ["IFNULL"](http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html). Not sure about the standard. – David Tonhofer Jul 14 '14 at 16:43
5

In DB2 the answer seems to be 'switch databases'. – rtf May 05 '17 at 22:21
4

For SQLite, it will be `IFNULL`. – Chintan Shah Sep 06 '17 at 05:52
@DavidTonhofer [COALESCE](https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_coalesce) also works in MySQL – Romuald Brunet Jun 24 '19 at 16:34

score 25 · Answer 2 · edited Jul 05 '18 at 02:35

In case of MySQL or SQLite the correct keyword is IFNULL (not 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'

score 13 · Answer 3 · edited Jun 20 '20 at 09:12

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

score 3 · Answer 4 · answered May 19 '21 at 16:16

3

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

answered May 19 '21 at 16:16

Partha Prateem Patra

163
7

Replace Default Null Values Returned From Left Outer Join

4 Answers4

MySQL

Linked

Related