SQL Server NOT excludes null?

Question

I have a column in my table called IsDuplicate, nullable.

If I want to return all results where IsDuplicate is '0' or is null, I can do

WHERE ISNULL(IsDuplicate, 0) = 0

or I can do

WHERE IsDuplicate = '0' OR IsDuplicate IS NULL

However, this never works:

WHERE NOT(IsDuplicate = '1')

Why does this not work? This should cover both IsDuplicate equaling both '0' and NULL.

IsDuplicate - why would you ever have this as NULL? It's either a duplicate, or it's not, right? Seems like it should not be nullable. And why are you sometimes comparing your column to 0, sometimes '0', and sometimes '1'? If it's a bit, you should just be comparing it to 0 or 1, never the characters '0' or '1'. — mason, Apr 03 '23 at 22:24
`NOT(IsDuplicate = '1')` does not return true or false with a NULL value. The result is UNKNOWN. — Dan Guzman, Apr 03 '23 at 22:25
Some reading here (https://stackoverflow.com/questions/9581745/sql-is-null-and-null) and here (https://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server) that goes some way to explaining. Maybe it matters less to understand exactly "why", rather than just to remember what the rule is! — Craig, Apr 03 '23 at 22:27
Its an interesting question, it would be easy to assume that it returns the records not returned by the condition inside, however the documentation says it "reverses the value of any Boolean expression." which still ignores nulls. — Dale K, Apr 03 '23 at 22:36
@mason this database is not my design. Every single value in our database is nullable. — Aashishkebab, Apr 04 '23 at 00:00
"*Every single value in our database is nullable*" then better get used to using `IS [NOT] NULL` — Paul Maxwell, Apr 04 '23 at 04:55
`WHERE NOT(IsDuplicate = '1')` is just a different way of saying `WHERE IsDuplicate <> '1'`. All that did was make the query appear confusing, which it apparently was given the question about NULL. — Zorkolot, Apr 04 '23 at 16:23

LordBaconPants · Answer 1 · 2023-04-03T22:33:59.037

2

Nulls aren't really a value. They are the absence value. You can see this in where clauses:

select * 
from foo 
where bar is null

instead of

select * 
from foo 
where bar = null

Bar could = 1, or it could be 34, or could have no value at all.

For example, I currently have 2 legs, 2 arms, and null tails. Is my tail a mouse tail? I don't have a tail to check against.

So for SQL, unless you specifically address the null they get ignored.

edited Apr 03 '23 at 22:33

answered Apr 03 '23 at 22:27

LordBaconPants

1,404
1
19
22

I would add “is my tail NOT a mouse tail?” Still don’t have a tail to check against. – Aaron Bertrand Apr 03 '23 at 22:41
I think its worth quoting from the documentation of "NOT" here to fully clarify e.g. "reverses the value of any Boolean expression." - because question is specifically about NOT, and it would be easy to assume that NOT will return the records not returned by the un-NOT-ed condition. – Dale K Apr 03 '23 at 22:43

score 1 · Answer 2 · edited Apr 04 '23 at 08:29

In SQL Server the treatment of comparisons and boolean operators involving NULL values are influenced by the setting of ANSI_NULLS option. When this option is ON, and the IsDuplicate is NULL the result of both IsDuplicate = '1' and NOT(IsDuplicate = '1') is UNKNOWN (not FALSE).

For a row to be returned the WHERE condition should evaluate to TRUE (not NULL or UNKNOWN).

Check these:
Results of boolean operators involving NULLs and UNKNOWNs https://learn.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql?view=sql-server-ver16

and how ANSI_NULLS option works
https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-ver16

And why NOT operator returns UNKNOWN from UNKNOWN:
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/not-transact-sql?view=sql-server-ver16

score 0 · Answer 3 · answered Apr 04 '23 at 05:14

In SQL, when comparing values, the result can be true or false or unknown. i.e. a "3 way logic" applies.

So, for NOT (some_value = 1) if some_value is NULL, the result of the expression will be unknown instead of true or false, and this means that rows with NULL values will not be returned by this expression.

i.e. for "where NOT(unknown)"; the NOT cannot reverse unknown into true or false, so it remains unknown and is therefore excluded from the result.

refer: The Three-Valued Logic of SQL

SQL Server NOT excludes null?

3 Answers3