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

1

Hello

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

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

Thanks!

2

[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
3

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

4

yes, NULLs will not be returned

5

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?

6

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') )