CODEWITHSUNDEEP
sqlsql-servert-sqlnull
ahmd0
ahmd0

Reputation: 17293

Comparing a value to a NULL in t-SQL

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?

Upvotes: 8

Views: 13507

Answers (6)

Andrea Nerla
Andrea Nerla

Reputation: 61

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.

Upvotes: 1

BradK
BradK

Reputation: 1

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

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

Upvotes: 0

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36591

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.

Upvotes: 9

whytheq
whytheq

Reputation: 35557

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

Upvotes: 0

craig65535
craig65535

Reputation: 3572

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.

Upvotes: 2

ASpirin
ASpirin

Reputation: 3651

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

Upvotes: 3

Related Questions