SQL Server return Rows that are not equal <> to a value and NULL

Question

I have a table that has a column of values that can be rowTypeID = (1,2,3, or null) . I would like to write a query that returns any row that doesn't have a value of 3 in it. In this example I want all NULL rows along with all 1,2 rows I just don't want rows with the value of 3

Set ANSI null ON is currently set for the database.

I'm curious as to why I can't write

select * from myTable where myCol <> 3

This query will not return any rows that have NULL in the myCol column

I have to write

select * from my Table where myCol <> 3 or myCol Is NULL 

Do I always have to include the IS NULL or can I set it up so a where clause myCol <>3 will return rows that have Null as value for my Col

Answer 1

I think your approach is fine:

SELECT *
FROM MyTable
WHERE myCol <> 3 OR myCol IS NULL

Since you are asking for alternatives, another way to do it is to make your column NOT NULL and store another (otherwised unused) value in the database instead of NULL - for example -1. Then the expression myCol <> 3 will match your fake-NULL just as it would with any other value.

SELECT *
FROM MyTable
WHERE myCol <> 3

However in general I would recommend not to use this approach. The way you are already doing it is the right way.

Also it might be worth mentioning that several other databases support IS DISTINCT FROM which does exactly what you want:

SELECT *
FROM MyTable
WHERE myCol IS DISTINCT FROM 3

MySQL has the NULL-safe equal which can also be used for this purpose:

SELECT *
FROM MyTable
WHERE NOT myCol <=> 3

Unfortunately SQL Server doesn't yet support either of these syntaxes.