SQL: Coalesce a thing of beauty
Author: jason
Date: 2014-01-23 14:02:10
Category: Musing

COALESCE can be used similar to the ISNULL function. It will return the first non-null value. I use it in WHERE clauses when I need to search more than 1 column, but when one of the incoming values can be null.

Its easier to see in the below query. The 1st value is set to NULL and the 2nd value is set to Space. With COALESCE of the 1st value is null, then it will be the value in the table. If value isn't null then match that value to the table. As we can see the 2nd value of Space matches with one of the rows in the table.




Code

DECLARE @One AS VARCHAR(10) = NULL
DECLARE @Two AS VARCHAR(10) = 'Space'

DECLARE @Temp AS TABLE ([id] BIGINT, [value] VARCHAR(10), [other] VARCHAR(10))
INSERT INTO @Temp SELECT 1, 'Space', 'Big'
INSERT INTO @Temp SELECT 2, 'Earth', 'Blue'
INSERT INTO @Temp SELECT 3, 'Mars', 'Red'

SELECT * FROM @Temp t
WHERE
COALESCE(@One, t.other) = t.other
AND COALESCE(@Two, t.value) = t.value


Be careful when you have actual NULL values inside the table. NULL does not equal NULL. It does not evaluate.




Wrap your table value with the ISNULL function and set it to something arbitrary. The query will evaluate correctly.



http://msdn.microsoft.com/en-us/library/ms190349.aspx



jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009