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.

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 NULL s and UNKNOWN s 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

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