mysql select query can not fetch data with null value

Question

In this table , if I make following query

select * from table where order_id != 1

I think, I am supposed to get row no 18 & 19. But instead, the query can't not fetch any row.

I can rewrite the query like this

select * from table where (order_id != 1 or order_id is null)

and it fetches the expected data, but should not the first query being able to fetch the row no 18 & 19?

mate, this is one of the to-know things, feature wise, you should use `<>` instead of `!=` as its not a straight forward binary comaprison. check these 2 threads and you should be good to go with better understanding! :) [thread_1](http://stackoverflow.com/questions/15501969/mysql-not-equals-to-isnt-working) , [thread_2](http://stackoverflow.com/questions/8994408/query-not-equal-doesnt-work) — NoobEditor, Jun 07 '16 at 05:16
`select * from table where order_id <> 1` doesn't work also, no row is picked — user2432443, Jun 07 '16 at 05:24
`select * from table where order_id <> 1 or order_id is NULL` , your query will not consider `NULL` rows buddy! :) — NoobEditor, Jun 07 '16 at 05:30

score 5 · Accepted Answer · answered Jun 07 '16 at 04:59

5

From Docs

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 operators

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL.

Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons.

answered Jun 07 '16 at 04:59

Praveen

8,945
4
31
49

yep, very surprising :) – user2432443 Jun 08 '16 at 04:05
Very true "IS NULL" or "IS NOT NULL" worked perfectly – Sumit Munot Dec 26 '18 at 05:57

score 1 · Answer 2 · answered Jun 07 '16 at 06:49

Null is Null not an infinite number!

Solutions:

Set a default value for the order_id; Especially if it's a foreign key and it's supposed to join with other tables.

Using IFNULL function Alter your query to this:

select * from table where ifnull(order_id,-1) != 1

score 0 · Answer 3 · edited May 23 '17 at 11:58

0

SQL operator != or <> only works on values. NULL is not a value - it is just absence of value.
Check this answer ,you might find this useful : Not equal <> != operator on NULL

edited May 23 '17 at 11:58

Community

1
1

answered Jun 07 '16 at 05:11

Niral Patel

16
2

mysql select query can not fetch data with null value

3 Answers3

Solutions: