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?
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.
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
WITH ctx AS(SELECT 123 AS n0, NULL AS n1) SELECT n0 FROM ctx WHERE ctx.n1 < 130 OR ctx.n1 is NULLIt 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.
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.
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
I've always used the EXISTS keyword along with EXCEPT like so
SELECT 1
WHERE EXISTS ((SELECT 1) EXCEPT (SELECT NULL))
