3

I have the following query where I'm looking for any rows that are not equal to 1 for the column istrue. However, the results only include records with 0 and omit those with null. While I am aware that using (istrue != 1 or istrue is null) would yield the expected outcome, I am curious about the underlying mechanism causing SQL Server to exclude null values. Could you please provide insights into this behavior? Thanks!

create table #test
(
id int,
val varchar(100),
istrue bit
)

insert into #test values 
(1, 'test-1', 1),
(2, 'test-2', 0),
(3, 'test-3', 1),
(4, 'test-4', 0),
(5, 'test-5', null),
(6, 'test-6', null)

select * from #test
where istrue != 1

drop table if exists #test
1

6 Answers 6

19

Consider checking documentation:

NULL indicates that the value is unknown. A null value is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a null value and any other value, return unknown because the value of each NULL is unknown.

5
  • 1
    This is the correct answer, but I think it could be improved slightly be mentioning that NULL coerces to FALSE as a boolean—the whole WHERE clause is essentially evaluated using an implicit IS TRUE (rather than IS NOT FALSE). That's the proximate cause of the records being omitted. Dec 12, 2023 at 16:48
  • 4
    The underlying issue, of course, is that the ANSI/SQL standard is misusing the word null, which does not mean ‘unknown’ or ‘unknowable’, but rather ‘empty/blank/not valid for use’. The expected meaning of a column being null, based on the word’s meaning, would be ‘this value is intentionally left blank/empty/unfilled (but its value is not unknown)’. But that’s not how it is, and we’re stuck with an unintuitive definition that gives rise to errors and questions like this one. Dec 13, 2023 at 2:31
  • 1
    @JanusBahsJacquet Exactly this! The basic problem with modern SQL databases, caused by some guy called Edgar Codd in the 1960 who first said NULL means "unknown", then changed his mind to "unknown or not applicable". Dec 13, 2023 at 4:58
  • E.g. I have a middle name, but the MiddleName field in your database is blank because you don't know what my middle name is! Once you fill in that field, it won't be NULL anymore. However for my wife, who doesn't have a middle name, how will you fill in that field? It's not applicable to her. The data is known (she doesn't have one), but you can't represent it because NULL could mean either "unknown" or "not applicable". Dec 13, 2023 at 5:00
  • @ReversedEngineer If the distinction between the two is of any significance (often, this is not the case), then an empty string can be used to represent a blank value. Another option is to have a separate field designating the status of the value or state of the system. For example, what percentage of a file has been downloaded? Before the download job runs, null means nothing. After the job runs, null means there was no file to download. To determine which meaning null has, you need to track whether the job has run or not.
    – Corrodias
    Dec 14, 2023 at 9:01
7

Aside from what's already been said, this isn't only a SQL Server implementation but rather follows the ANSI standard, so other database systems have similar behavior.

If you have a use case where you really want to treat NULL values alike AND one of the NULL values is a variable or literal, then one thing you can do is turn off the ANSI_NULL logic before running your query:

SET ANSI_NULLS OFF;

This will apply at the session level. Here's a dbfiddle.uk example.

But unfortunately this doesn't help your example case, since you're trying to compare where a column is NULL to a non-null literal.

In your case, you'd have to explicitly check for IS NULL on the column, as you mentioned, or you can use a function such as ISNULL() to wrap the column and supply a replacement value that makes sense. For example: ISNULL(istrue, 0) != 1

0
6

If you consider that the value of istrue is unknown in the NULL case then it might or might not equal 1.

The expression istrue != 1 then evaluates to unknown

SQL only returns rows where the WHERE clause evaluates to true.

If you are on SQL Server 2022+ you can use

WHERE istrue IS DISTINCT FROM 1

To give the inequality semantics that you want (Fiddle).

2

SQL Server does not return NULLs when using the not equal operator (<>) or the inequality operator (!=) because NULL is not a value that can be compared with other values.

NULL means unknown or missing value, so any comparison with NULL results in unknown or false

Therefore, if you want to include NULLs in your query results, you need to use the IS NULL predicate in addition to the not equal operator

select * from #test
where istrue != 1 or istrue is NULL
1

SQL uses three valued logic. The possible outcome for an operation are:

  • true
  • false
  • unknown

In SQL:

  • unknown is represented by null
  • any operation involving unknown results in unknown (in general)
  • unknown is not the same as false

In this very simple query where istrue != 1 the predicate returns unknown where istrue is null. In fact, you get unknown for where not (istrue = 1) as well. Such rows do not match the where clause.

Solutions: use is distinct from operator if your RDBMS supports it (part of SQL standard, a non-standard <=> operator might be be available). Otherwise use is null to add a special case for unknown values:

where istrue is null or istrue != 1
-4

What I am going to type is already embodied but not quite stated in more than one of the other answers.

It looks to me like you are crossing a certain simple, but not obvious, crucial bridge in your walk along SQL Road. The bridge, of course, is the Bridge of NULL. I lived through much agony on Bridge of Null. My goal is to help you across it with the least agony possible. They way across Bridge of Null is not complicated, but it is stupid, left over from the days when relational databases were being developed and those nerds (great people, but still nerds) made nulls part of relational data religion and no one has bothered removing it from sql.

HOW TO CROSS BRIDGE OF NULL Answers above go into some of the technical justification and reasons. The one thing that I don't yet see in any of the answers is great rule of NULLs NOT USED IN WHERE or IF CLAUSES.

In where and if clauses, everything except nulls work the same, like this

Where or if expressionA = expressionB or expressionA > expressionB or expressionA < expressionB or expressionA <> expressionB or expressionA like expressionB or someotherexpression equals or is less than or is more than or is like

EXCEPT NULLS! If you want to compare the value of null to any other value, the result is not what you want. Null does not compare with anything but itself. Something is null or it is not null.

That is worth repeating.

Null does not compare with anything but itself. Something is null or it is not null.

The simple rule to remember is if you want nulls in your data, use

where or if fieldvalue is null

if you do not want nulls in your data, use where or if fieldvalue is not null

You see any = signs where null is referenced above? Answer: No you do not.

Do you see = signs where null is referenced or being looked for in your sql?
Answer: I hope you do, becausae I see it.

RULE. = or an other comparison operator with null is .. I dunno, never what I need it to be. Never use one with null.

In the case of your sql, where it says istrue !=1, you are thinking null does not equal 1 and that is an error! Null does not equal or not equal anything. Maybe null = null but there is never a need to use that.

Null does not equal anything. Something is null or it is not null.

A frequent way of dealing with this is having a statement like this

if istrue !=1 or istrue is null

or if you want

if istrue !=1 or istrue is not null

If you do not cross this bridge, you will never do much with relational databases.

Good luck. Welcome to world of database expertise. When you handle nulls like no problem, you will be a different person, one who might help save the world from the insanity infecting it today.

3
  • Hi Dan, would you please format your answer, it is impossible to read it as it stands. make a list, separate it into sections, use bold and codefencing (backticks) etc. Dec 12, 2023 at 12:01
  • 3
    The concept of NULL is not stupid - it's an important part of relational database design which is based on set theory.
    – DJ.
    Dec 12, 2023 at 17:29
  • 1
    So, what would you put in the ModifiedDate column of a record that has never been modified?
    – Kevin
    Dec 12, 2023 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.