Need info on ISNULL

Transact-SQL
1

HI..
I come across this--> a lot AND ISNULL(appt_type,'') <> 'A' I know that ISNUILL function replaces a null with blank in this case but what does the <> 'A' or something within quotation '' exactly do in this case? confusing to me.. seen a lot of people use this as well.

Thanks!
Pasi.

2 
ISNULL(appt_type,'') <> 'A' 

lets says appt_type is null then it gets replaced by ''   
which is basically an empty string 

then empty string is compared with 'A' whether it is not equal to 

= the condition is TRUE
1 Like
3

That is the wrong way to code that. You should do this instead:

WHERE (appt_type IS NULL OR appt_type <> 'A')

1 Like
4

Thanks Scott.. this looks better but seen that a lot with other peoples code..

5

Thanks harishgg1 for explanation..

6

Yes, you'll see that a lot, drives DBAs (like me) crazy, since it's "nonsargable".

You will also often see this style:

WHERE ISNULL(appt_type,'') <> ''

That's even easier to fix, just change it to:

WHERE appt_type <> ''

NULL is never "=" or "<>" to anything, so that will produce the same results as the original code but without the function that makes it less efficient.

1 Like
7

Thanks Scott always learning from you!

8

Both basic methods suck for performance because one is Non-SARGable in a WHERE clause and the other contains a OR.

Use a WHERE IN with the inclusive list of appt_type(s) instead. If you feel you must, an WHERE EXISTS would also work. And appt_type shouldn't be equal to NULL to begin with. That's a data problem that should be repaired in what's stored in the table.

9

Thanks Jeff!