Ryan Haley Ryan Haley - 8 days ago 5
SQL Question

SELECT DISTINCT is omitting NULL values when not desired

I am trying to build a distinct string in a query, which works unless one of the values is NULL. I've tested removing LOCATION_ADDR_LINE_2, and the query will work just fine. When I do not SELECT DISTINCT, I find that LOCATION_ADDR_LINE_2 values are NULL. How can I gather these values in the SELECT DISTINCT even if NULL?

SELECT DISTINCT(LOCATION_ADDR_LINE_1 + ', ' + LOCATION_ADDR_LINE_2 + ', ' + LOCATION_CITY + ', ' + LOCATION_WORK_STATE) AS Addresses
FROM OracleReport
WHERE (LOCATION_ADDR_LINE_1 LIKE '%1135 Auto%' OR LOCATION_ADDR_LINE_1 LIKE '%199 Easy%')


Returns:

Addresses
NULL

SELECT DISTINCT(LOCATION_ADDR_LINE_1 + ', ' + LOCATION_CITY + ', ' + LOCATION_WORK_STATE) AS Addresses
FROM [OperationReport].[dbo].[OracleReport]
WHERE (LOCATION_ADDR_LINE_1 LIKE '%1135 Auto%' OR LOCATION_ADDR_LINE_1 LIKE '%199 Easy%')


Returns:

Addresses

1135 Auto...

189-199 Easy...

Answer

Assuming you don't mind text,,text,... (empty string) when a value is NULL...

SELECT  DISTINCT(coalesce(LOCATION_ADDR_LINE_1,'') + ', ' +              
                 coalesce(LOCATION_ADDR_LINE_2,'') + ', ' + 
                 coalesce(LOCATION_CITY,'')  + ', ' + 
                 coalesce(LOCATION_WORK_STATE,'')) AS Addresses
FROM  OracleReport
WHERE (LOCATION_ADDR_LINE_1 LIKE '%1135 Auto%' 
    OR LOCATION_ADDR_LINE_1 LIKE '%199 Easy%')

Coalesce will take the first non-null value and return it. It requires consistent data types and will early exit once the first non-null value in a series is encountered. (more details Oracle Differences between NVL and Coalesce)