SQL BUG doesn't accept null when write !=

Question

When I write column!=something it also doesn't choose any null value and something. Why this happened? Can I prevent this just writing AND column is NULL?

Because `null` will allways be not fullfilling the condition if it is not beeing checked explicity. — SomeJavaGuy, Aug 08 '16 at 06:12

1000111 · Accepted Answer · 2016-08-08T06:17:26.840

2

You need to check for NULL too.

SELECT 
*
FROM your_table
WHERE column <> something
OR column IS NULL

Note: You cannot compare NULL with anything but NULL.

Let's look at the following code snippets:

SET @v := NULL;
SELECT @v = NULL;
Result: NULL; Because NULL can be only compared using IS NULL

SELECT @v IS NULL;
Result: 1 (i.e. TRUE)

The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.

To test for NULL, use the IS NULL and IS NOT NULL

WORKING WITH NULL VALUES IN MYSQL

edited Aug 08 '16 at 06:17

answered Aug 08 '16 at 06:10

1000111

13,169
2
28
37

1

Thank you for answering.First sentence was answer for me :) – Some like you Aug 08 '16 at 13:33

score 1 · Answer 2 · edited May 23 '17 at 12:08

1

Yes, you have check for NULL using the OR operator as 1000111 specified, not AND.

Also as you already know when using OR, if there are other AND operators in the WHERE clause then we need to enclose them with brackets ()

Regards

edited May 23 '17 at 12:08

Community

1
1

answered Aug 08 '16 at 06:20

Amitav Mohanty

11
4

score 1 · Answer 3 · answered Aug 08 '16 at 06:39

1

Need to check for NULL condition separately..

and column is NULL

answered Aug 08 '16 at 06:39

Harika B

83
9

Thanks for your answer I edit your answer a little bit – Some like you Aug 08 '16 at 13:29
Okay..sure. Thanks – Harika B Aug 10 '16 at 06:37

SQL BUG doesn't accept null when write !=

3 Answers3