concatenation of null values in SQL from a join of tables

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.

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:

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