NULL IN List

Transact-SQL
1

Why can't I write something like this (with NULL included in the list):
(My_Code NOT IN ('Hi', NULL)

Is this the only way to do this? This works:
(My_Code <> 'Hi' AND My_Code IS NOT NULL)

2

You can't have a NULL in the list, the list can then never match.

You don't need to check for NULL, because NULL can never be "NOT IN" any list of values.

Thus, just:
My_Code NOT IN ('Hi')

1 Like
3

hi

it will work if you put some other value for null

ISNULL(column,12345) = ISNULL(columnABC,12345)

you cannot directly compare NULLS but this way you can

4

For performance reasons, I'd choose:

but you could do (if thats easier to read):
ISNULL(My_Code,'Hi')!='Hi'

5

ISNULL(column,12345) = ISNULL(columnABC,12345)

NO!

This will hurt performance, possibly very significantly, and you don't need to do it.

If you are comparing a column = to a value(s) or <> to a value(s), then you do not need to test separately for NULL. NULL will never be = or <> any value.

Just:
My_Code NOT IN ('Hi')
will exclude all values that are not 'Hi' and all values that are NULL.