SQLTeam.com | Weblogs | Forums

Does [Col1] <> 'VALUE' return NULLs or not?


Quick question on the reasonable but confusing compared to other software, three value principle in SQL:

Does [Col1] <> 'VALUE' return NULLs or not?


[Col1] <> 'VALUE' is a logical expression, which will be evaluated to TRUE, FALSE, or UNKNOWN.
If Col1 is null, the expression evaluates to UNKNOWN.

1 Like

So I understand that NULLs will not be returned, right?

yes, NULLs will not be returned

Thanks, is there a neat way to say:
[Col1] is not in ('a','b') or NULL?

Instead of having to write:
[Col1] is not in ('a','b') or [Col1] is not NULL?

Not really - any methods that can be used would make the argument non-SARGable and eliminate any possible use of an index if one exists. The best method is:

WHERE ( [Col1] Is Null OR [Col1] NOT IN ('a', 'b') )