Geek Answers Handbook

SQL: Why are NULL values ​​filtered inside this where clause?

In my table, I have a column with a zero bit (inherited system ...), and another developer recently made changes to the stored procedure to show only values ​​in which the bit column was not right (1). Since this is a nullable column, we noticed that if the column was NULL, the record was not collected. Why is this?

Both the developer and I agree that NULL <> 1 ... Is this an error in SQL or was it designed that way? Looks like a design flaw.

Current code:

(VoidedIndicator <> 1)

Suggested fix:

(VoidedIndicator <> 1 OR VoidedIndicator IS NULL)

Clarification (by John Erickson)

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

SQL- where, (VoidedIndicator < > 1), , VoidedIndicator == 0, VoidedIndicator == 0, VoidedIndicator IS NULL. ?

+5
7

NULL:

, WHERE . , "" " " , Null, , , Unknown. :

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

, NULL - , = < > , .

MSDN T-SQL <> :

( ). nonnull , , ; . NULL, . SET ANSI_NULLS (Transact-SQL).

SET ANSI_NULLS :

SET ANSI_NULLS , SELECT , WHERE column_name = NULL , . SELECT, WHERE column_name < > NULL column_name.

...

SET ANSI_NULLS , . SET ANSI_NULLS , TRUE, NULL.

+11

, .

SQL, Null " ", " "

, SQL .

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
+12

.

NULL , NULL (NULL = NULL FALSE).

NULL . , NULL. , , , NULL, - .

+3

, NULL <> 1 , WHERE.

, , :

(VoidedIndicator <> 1 OR VoidedIndicator IS NULL)

SQL-99 , , IS DISTINCT FROM:

(VoidedIndicator IS DISTINCT FROM 1)

, . , Microsoft SQL Server IS DISTINCT FROM.

+1

: isnull (VoidedIndicator, 1) < > 1

0

WHERE , true.

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

This is why you are encouraged to avoid NULL columns whenever possible.

0
source

NULL <> 1 evaluates (theoretically) the value of "possible", which means that the record will not be returned.

0
source

More articles:


All Articles