concatenation of null values in SQL from a join of tables

Posted At : 12 May 2007 13:35 | Posted By : Duncan Loxton
Related Categories: SQL

Today we were trying to join 2 fields from different tables with a left outer join. This means that if there is nothing in the joining table the rows are returned as null.

By default SQL wont give you a result in the joining request.

Lets have an example. You want to get categories and their parents one level deep.

select distinct
   (select CategoryName +'/'
    from Category
    where CategoryID = epc.CategoryParentID
   ) + CategoryName as CategoryName
from Category epc

This would only render the rows that have a child category - i.e. where the CategoryParentID is not null.

To solve this issue and get the categories at the top level as well, we need to tell SQL to resolve nulls in a concatenation, rather than ignore them:

set CONCAT_NULL_YIELDS_NULL OFF
select distinct
   (select CategoryName +'/'
    from Category
    where CategoryID = epc.CategoryParentID
   ) + CategoryName as CategoryName
from Category epc

This sql variable tells the process to allow nulls on one side of the join.

set CONCAT_NULL_YIELDS_NULL OFF