views:

1853

answers:

7

In my table, I have a nullable bit column (legacy system...) and another developer recently made a change to a stored procedure to only show values where the bit column was not true (1). Because this is a nullable column, we noticed that if the column was NULL, the record was not being picked up. WHY is this?

Both the other developer and I agree that NULL <> 1... Is this a bug in SQL or was this designed this way? Seems like a design flaw.

Current Code:

(VoidedIndicator <> 1)

Proposed Fix:

(VoidedIndicator <> 1 OR VoidedIndicator IS NULL)

Clarification (By Jon Erickson)

VoidedIndicator is a nullable bit field so it can have the following values: NULL, 0, or 1

When a SQL statement is created with a where clause such as (VoidedIndicator <> 1) we only get records returned that have VoidedIndicator == 0, but we were expecting both VoidedIndicator == 0 and VoidedIndicator IS NULL. Why is this?

+3  A: 

It's not a bug.

NULL is not equal to anything, not even NULL (NULL = NULL returns FALSE).

Typically NULL values aren't indexed either. It's generally a bad idea to rely on a particular value or NULL. Depending on what you're storing in the column, you may be better off putting a dummy or sentinel value in rather than using NULL to indicate some meaning.

cletus
So if NULL is not equal to anything as you say, then how does NULL not get included in this? Column <> 1... NULL does not equal 1? Correct?
RSolberg
Any comparison involving NULL returns false so yes NULL <> 1 is false. That's what IS NULL or IS NOT NULL is for.
cletus
The best way I find to think about nulls in database terminology, rightly or wrongly, is that a null value is "unknown", therefore cannot compared to another null value, nor compared to a concrete value
Russ Cam
@cletus: It's more accurate to say that NULL<>1 is "unknown" because if it were false, then NOT(NULL<>1) would be true. Instead, applying NOT to unknown is still unknown.
Bill Karwin
+6  A: 

From the Wikipedia entry on NULL:

For example, a WHERE clause or conditional statement might compare a column's value with a constant. It is often incorrectly assumed that a missing value would be "less than" or "not equal to" a constant if that field contains Null, but, in fact, such expressions return Unknown. An example is below:

-- Rows where num is NULL will not be returned,
-- contrary to many users' expectations.
SELECT * FROM sometable WHERE num <> 1;

Basically, any comparison between NULL and something else, whether it's with = or <> will not be true.

As another reference, the MSDN T-SQL page on <> states:

Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE. If either or both operands are NULL, see SET ANSI_NULLS (Transact-SQL).

The SET ANSI_NULLS page then states:

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

...

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.

Jon Skeet
+1  A: 

You can also: isnull(VoidedIndicator,1) <> 1

eglasius
Bad idea for performance unless you have a functional index for this sort of thing.
cletus
I was going to say, I thought IsNull() had performance concerns...
RSolberg
@cletus that is considering they have an scenario that actually needs that optimization. I do agree on using a default value where possible/appropriate though.
eglasius
A: 

Because the WHERE clause only selects rows when the condition evaluates to true.

When one of the operands is NULL, the condition usually evaluates to UNKNOWN (approximately equivalent to NULL), and therefore is not true. It applies to both 'column = 1' and 'column <> 1'; if column is NULL, the search condition fails.

It is why you get told to avoid NULL columns whenever possible.

Jonathan Leffler
Do you also avoid the integer value zero, since division by zero causes an error?
Bill Karwin
It might depend on the server, but the chances are decent that if you write 'column / 0' and column is null, then the division code looks to see if either operand is null and returns null before generating the 'divide by zero' error. However, if you know 'column' is null, why are you dividing it?
Jonathan Leffler
And why are you dividing by zero, anyway? I think it would be unwise to rely on nulls preventing divide by zero errors, but if you divide a null by zero 'accidentally', then you probably don't get the error.
Jonathan Leffler
I think you misunderstood me... You say to avoid NULL, because NULL has some special logic you have to handle. But this would be like prohibiting zero, just because you have to handle zero specially in a division operation. Just suck it up and do the checks!
Bill Karwin
Yes; I completely misinterpreted the question. No, I don't avoid zero; I merely avoid division by zero. I avoid null when I can, which is most (but not all) of the time.
Jonathan Leffler
A: 

The other folks are correct that NULL <> 1 doesn't evaluate as true, therefore it doesn't satisfy the WHERE clause.

The proposed fix you describe is the best way of handling it:

(VoidedIndicator <> 1 OR VoidedIndicator IS NULL)

SQL-99 does have a predicate that helps in this case, called IS DISTINCT FROM:

(VoidedIndicator IS DISTINCT FROM 1)

This predicate would behave exactly the same as your proposed fix. Unfortunately, Microsoft SQL Server does not support IS DISTINCT FROM yet.

Bill Karwin
Thanks for the insight, I'd not come across IS DISTINCT FROM before. So this still hasn't been implemented in SQL Server 2008?
Russ Cam
Right. I cannot find IS DISTINCT FROM in the MS SQL Server 2008 online documentation, whereas I do find the IS NULL predicate and the ISNULL() function.
Bill Karwin
PostgreSQL, IBM DB2, and Firebird support IS DISTINCT FROM. Oracle and Microsoft do not. MySQL supports an operator "<=>".
Bill Karwin
A: 

NULL <> 1 evaluates (theoretically) to "maybe", which means the record will not be returned.

erikkallen
+8  A: 

Lots of good answers, but let me give you a really concise version.

To SQL, Null does NOT mean "No value" it means "Unknown Value"

With that in mind, consider the answer to the question you are asking SQL in plain English.

Q: Is this unknown value not equal to 1? 
A: I don't know, there is no way to tell without knowing the value.

Hence Null<>1 = Null
JohnFx