Best way to write union query when dealing with NULL and Empty String values

Question

I have to write a query that performs a union between two tables with similar data. The results need to be distinct. The problem I have is that some fields that should be the same are not when it comes to empty values. Some are indicated as null, and some have empty string values. My question is, is there a better way to perform the following query? (without fixing the actual data to ensure proper defaults are set, etc) Will using the Case When be a big performance hit?

Select  
    When Column1 = '' Then NULL Else Column1 as [Column1],
    When Column2 = '' Then NULL Else Column2 as [Column2]
From TableA

UNION ALL

Select 
    When Column1 = '' Then NULL Else Column1 as [Column1],
    When Column2 = '' Then NULL Else Column2 as [Column2]
From TableB

Answer 1

A Case should perform fine, but IsNull is more natural in this situation. And if you're searching for distinct rows, doing a union instead of a union all will accomplish that (thanks to Jeffrey L Whitledge for pointing this out):

select  IsNull(col1, '')
,       IsNull(col2, '')
from    TableA
union
select  IsNull(col1, '')
,       IsNull(col2, '')
from    TableB

Answer 2

I don't think it would make any difference in performance, but NULLIF is another way to write this and, IMHO, looks a little cleaner.

Select  
    NULLIF(Column1, '') as [Column1],
    NULLIF(Column2, '') as [Column2]
From TableA

UNION

Select 
    NULLIF(Column1, '') as [Column1],
    NULLIF(Column2, '') as [Column2]
From TableB

Answer 3

You can keep your manipulation operations separate from the union if you do whatever manipulation you want (substitute NULL for the empty string) in a separate view, then union the views.

You shouldn't have to apply the same manipulation on both sets, though.

If that's the case, union them first, then apply the manipulation to the resulting, unioned set once.

Half as much manipulation code to support that way.

Answer 4

Use UNION to remove duplicates - it's slower than UNION ALL for this functionality:

SELECT CASE 
         WHEN LEN(LTRIM(RTRIM(column1))) = 0 THEN NULL
         ELSE column1
       END AS column1,
       CASE 
         WHEN LEN(LTRIM(RTRIM(column2))) = 0 THEN NULL
         ELSE column2
       END AS column2
  FROM TableA
UNION 
SELECT CASE 
         WHEN LEN(LTRIM(RTRIM(column1))) = 0 THEN NULL
         ELSE column1
       END,
       CASE 
         WHEN LEN(LTRIM(RTRIM(column2))) = 0 THEN NULL
         ELSE column2
       END 
  FROM TableB

I changed the logic to return NULL if the column value contains any number of spaces and no actual content.

CASE expressions are ANSI, and more customizable than NULLIF/etc syntax.