Select Not Including Nulls when not explicitly stated

Question

When I use

Select Distinct Brand_ID
from db.ABC
WHERE
Brand_ID <> 800

I get the returned output:

+----------+
| Brand_ID |
+----------+
|      100 |
|      200 |
|      300 |
|      400 |
|      500 |
|      600 |
|      700 |
|      900 |
+----------+

However, the column does contain NULL Values, which are present. I need to update my select statement to explicitly say:

Select Distinct Brand_ID
from db.ABC
WHERE
Brand_ID <> 800 OR Brand_ID IS NULL

To get the correct output:

+----------+
| Brand_ID |
+----------+
| NULL     |
| 100      |
| 200      |
| 300      |
| 400      |
| 500      |
| 600      |
| 700      |
| 900      |
+----------+

Why does the NULL value get removed when not explicitly stated?

NULL is the absence of value. In this case it is not <800 nor > 800 — John Cappelletti, Jun 11 '18 at 19:07
https://stackoverflow.com/questions/11491831/null-values-are-excluded-why — sgeddes, Jun 11 '18 at 19:07
@GregdeLima This functionality always annoyed me until I really thought about what NULL means. NULL signifies that a value is unknown or unavailable. So True/False value statements are not defined. If I don't know how much money I have, I have NULL money. That's not the same as having no money. I may have 27 dollars, I may not, so the truth value of "I have 27 dollars" is unknown rather than true or false. — Error_2646, Jun 11 '18 at 20:47
@Error_2646 - 100% agreed, just needed to reframe the thought. — GregdeLima, Jun 12 '18 at 12:16

score 5 · Accepted Answer · answered Jun 11 '18 at 19:06

5

SQL NULL means

I don't know

so NULL isn't a value.

answered Jun 11 '18 at 19:06

D-Shih

44,943
6
31
51

1

So simple, but never thought about it. – The Impaler Jun 11 '18 at 19:08
@TheImpaler Yes `NULL` is a special one in SQL – D-Shih Jun 11 '18 at 19:11
Yeah, silly of me. – The Impaler Jun 11 '18 at 19:12
simple and awesome! – Wei Lin Jun 12 '18 at 11:35

Tab Alleman · Answer 2 · 2018-06-11T19:18:14.723

1

Why does the NULL value get removed when not explicitly stated?

Because it is not true that NULL <> 800

As an unknown quantity, NULL might equal 800 or it might not. The only thing known about it is that it IS NULL. Any equality or non-equality test of NULL will always return false.

EDIT: Here's a bizarre flash realization...NULL is the Schroedinger's Cat of the SQL Universe. : )

edited Jun 11 '18 at 19:18

answered Jun 11 '18 at 19:09

Tab Alleman

31,483
7
36
52

score 1 · Answer 3 · answered Jun 11 '18 at 19:12

That's how SQL NULL comparison works. The NULL is eliminated in your where clause. This is also because of SET ANSI_NULL ON setting. By default it is ON.

See an excellent explanation here

You can try your query by stating

SET ANSI_NULL OFF
GO
Select Distinct Brand_ID
from db.ABC
WHERE
Brand_ID <> 800

This will give correct output

Select Not Including Nulls when not explicitly stated

3 Answers3