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)

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


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

For performance reasons, I'd choose:

but you could do (if thats easier to read):

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


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.

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