unusual sql server query result

Question

Let's say I have a table called nameAge:

ID  Name  Age
1   X     12
2   Y     12
3   null  null
4   Z     12

and when I run a query like:

select * from nameAge where Age <> 12

it returns me an empty result set while I have row with id 3 where age is different than null?

Using Sql Server 2008 R2.

Any ideas?

Edit: Possibility to be duplicate with suggested answer may be at one point but does not cover at all and it shows how to use null values when compared with null but what I wanted to ask was about the result set which includes null values

possible duplicate of [Not equal <> != operator on NULL](http://stackoverflow.com/questions/5658457/not-equal-operator-on-null) — chue x, Jun 02 '15 at 12:08

score 16 · Accepted Answer · edited Jul 05 '15 at 19:58

16

This is the intended behavior. You cannot compare NULL values using = or <>. You have to use IS NULL or IS NOT NULL.

If you want NULL values only use IS NULL:

select * from nameAge where age IS NULL

If you want NULL values with age <> 12 values, use:

select * from nameAge where age <> 12 OR age IS NULL

edited Jul 05 '15 at 19:58

potashin

44,205
11
83
107

answered Jun 02 '15 at 06:44

ughai

9,830
3
29
47

1

Note you can use `=` and `<>` when `ANSI_NULLS` is off. – Ash Burlaczenko Jun 02 '15 at 09:21
**An important addendum:** The check `NULL <> 12` will not return false, but will return a boolean NULL. Which is treated like a false value, but **can not be negated** So `NOT ( NULL <> 12 )` is still boolean NULL and not true. – Falco Jun 02 '15 at 12:03
1

@Falco - You are absolutely correct, this is described in detail by the answer provided by Tim Biegeleisen and Giorgi Nakeuri – ughai Jun 02 '15 at 12:08

Tim Biegeleisen · Answer 2 · 2015-06-02T13:12:54.653

8

The expression

WHERE NULL <> 12

does not return TRUE or FALSE, but actually returns UNKNOWN. This means that the third record in your table will not be returned by your query.

As @ughai mentioned, you should use IS NULL instead to query that record:

SELECT * FROM nameAge WHERE age IS NULL

Have a look at the Microsoft SQL Server documentation for more information.

edited Jun 02 '15 at 13:12

answered Jun 02 '15 at 06:46

Tim Biegeleisen

502,043
27
286
360

2

According to the SQL Standard, the result is `UNKNOWN` rather than `NULL`. [tag:mysql] conflates `NULL` and `UNKNOWN` but most other database products (that I'm aware of) do not. – Damien_The_Unbeliever Jun 02 '15 at 13:06
@Damien_The_Unbeliever Thanks for pointing this out, and I have updated my answer with a link to the official Microsoft documentation. – Tim Biegeleisen Jun 02 '15 at 13:13

score 4 · Answer 3 · answered Jun 02 '15 at 09:16

When you are dealing with NULLs you should be always careful because of 3 valued logic used in Sql Server(when a predicate can be evaluated to TRUE, FALSE or UNKNOWN). Now here is a classic select statement where many newcomers make a mistake, suggesting that the statement would return all rows where Age <> 12 including NULLs.

But if you know the easy fact that comparing NULL to any value, even to NULL itself will evaluate to UNKNOWN it is getting more clear what is going on. WHERE clause will return ONLY those rows where predicate is evaluated to TRUE. Rows where predicate evaluates to FALSE or UNKNOWN will be filtered out from resultset.

Now let's see what is going on behind the scene. You have 4 rows:

ID    Name    Age
1     X       12
2     Y       12
3     null    null
4     Z       12

and the predicate is:

where Age <> 12

When you evaluate this predicate for each row you get:

ID    Name    Age   Evaluation result
1     X       12    FALSE              --(because 12 <> 12 is FALSE)
2     Y       12    FALSE              --(because 12 <> 12 is FALSE)
3     null    null  UNKNOWN            --(because NULL <> 12 is UNKNOWN)
4     Z       12    FALSE              --(because 12 <> 12 is FALSE)

Now remember that WHERE clause will return only rows where predicate evaluates to TRUE and it is clear that you will not get any result because no row evaluates to TRUE.

unusual sql server query result

3 Answers3