coalesce in a where clause

Question

coalesce in a where clause

joanna.seldon

Ten Centuries

Points: 1188
More actions
December 6, 2016 at 7:52 am

#401166

Hi
having difficulty trying to write a coalesce within a where clause.
is with a value and a null
I am trying to write
Where (coalesce(A.Department,B.Department) <> '1', NULL)
I wish to bring back rows of data for Department <> '1' , and Null
Please help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

joanna.seldon (12/6/2016)
I am trying to write
Where (coalesce(A.Department,B.Department) <> '1', NULL)
I wish to bring back rows of data for Department <> '1' , and Null

Can you try:

Where Coalesce(A.Department, B.Department, '0') <> '1'

The coalesce function returns the first non-null value from the list. If both A.Dept and B.Dept are null then '0' gets returned. If '0' is not acceptable, you could return anything you would like including the word 'NULL'.

Having said that, the rule is generally you don't want functions on the left side of a comparison operator (sargability issues). You will have to test to make sure that speed is acceptable on a larger data set.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

You don't need coalesce here. NULL values are neither equal nor unequal when compared to other because NULL is unknown. Does a bag with an unknown number of apples have more than 1 apple?

If you just want records where A.Department is not null and not equal to 1 you could go with:

WHERE A.Department <> 1

If you want records where A.Department is not null or equal to 1 or A.Department is null but B.Department is not null and not equal to 1 you could go with

WHERE coalesce(A.Department,B.Department,1) <> 1

But here, again, you would not need COALESCE or ISNULL - you could go with

WHERE 1 IN (A.Department, B.Department)

Now let's say you wanted records where A.Department was null or any value other than 1 you could go with

WHERE COALESCE(A.Department, 0) <> 1

but this would probably be better:

WHERE A.Department IS NOT NULL AND A.Department <> 1

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

joanna.seldon Ten Centuries Points: 1188 More actions · Answer 3

joanna.seldon

Ten Centuries

Points: 1188

December 7, 2016 at 5:18 am

#1916473

Hi

thanx amazing..worked a treat

Kind Regards

LinksUp SSCertifiable Points: 6526 More actions · Answer 4

joanna.seldon (12/7/2016)
Hi
thanx amazing..worked a treat
Kind Regards

Just curious, but which solution worked for you?

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

joanna.seldon Ten Centuries Points: 1188 More actions · Answer 5

yours x

Where Coalesce(A.Department, B.Department, '0') <> '1'

thanx again