Comparing a value to a NULL in t-SQL

Question

I was curious if it's legal in t-SQL to compare a NULL to a value?

For instance, if I have:

WITH ctx AS(SELECT 123 AS n0, NULL AS n1)
SELECT n0 FROM ctx
WHERE ctx.n1 < 130

the WHERE clause in that case is always evaluated as FALSE. Is it something I can rely on?

Daliladalis

Answer 1

You can't compare NULL with any other value, it will result in 'UNKNOWN'.

From msdn source

A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

Corticosteroid

Answer 2

All boolean operations in T-Sql with null value returns 'UNKNOWN', which is recognized as false in clauses. You can use ISNULL function when you want set some default value. for example in your case:

WITH ctx AS(SELECT 123 AS n0, NULL AS n1)
SELECT n0 FROM ctx
WHERE isnull(ctx.n1,0) < 130

Remote

Yeah, also the check 'is null' works: WITH ctx AS(SELECT 123 AS n0, NULL AS n1) SELECT n0 FROM ctx WHERE ctx.n1 < 130 OR ctx.n1 is NULL – Orangutan

Answer 3

It depends on the value of ANSI_NULLS.

http://msdn.microsoft.com/en-us/library/ms191270%28v=sql.90%29.aspx

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN.

Transact-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF. When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL.

Node

Answer 4

As you can see in this answer, in many SQL engines you can use the IS DISTINCT FROM clause which will always return a True or False value.

More info: Most SQL engines use Three-valued logic. This means that a conditional can return True, False or, in case of comparison with a NULL value, Unknown.

So, as an example, in a table like

Col
1
2
NULL

A query like

SELECT * FROM table WHERE col <> 1

would return

Col
2

since 1 <> NULL doesn't return True but Unknown.

Instead, a query like

SELECT * FROM table WHERE col IS DISTINCT FROM 1

would return

Col
2
NULL

since 1 IS DISTINCT FROM NULL returns True.

Chenault

Maybe you could edit your answer and provide an example that uses IS DISTINCT FROM? – Extraditable

Answer 5

The WHERE clause in the following = is also FALSE. You need to be very careful with NULLs

WITH ctx AS
(
SELECT 123 AS n0, NULL AS n1
) 
SELECT *
FROM ctx 
WHERE ctx.n1 = NULL

Nalchik

Answer 6

I've always used the EXISTS keyword along with EXCEPT like so

SELECT 1
WHERE EXISTS ((SELECT 1) EXCEPT (SELECT NULL))

Chaperone