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 , thread_2 — NoobEditor
– NoobEditor, Commented Jun 7, 2016 at 5:16
select * from table where order_id <> 1 doesn't work also, no row is picked — user2432443
– user2432443, Commented Jun 7, 2016 at 5:24
select * from table where order_id <> 1 or order_id is NULL , your query will not consider NULL rows buddy! :) — NoobEditor
– NoobEditor, Commented Jun 7, 2016 at 5:30

Praveen · Accepted Answer · 2016-06-07 04:59:40Z

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 7, 2016 at 4:59

Praveen

9,4634 gold badges38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2432443 Over a year ago

yep, very surprising :)

Sumit Munot Over a year ago

Very true "IS NULL" or "IS NOT NULL" worked perfectly

Amir Hajizadeh · Accepted Answer · 2016-06-07 06:49:25Z

1

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

answered Jun 7, 2016 at 6:49

Amir Hajizadeh

365 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:58:44Z

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, 2017 at 11:58

CommunityBot

11 silver badge

answered Jun 7, 2016 at 5:11

Niral Patel

162 bronze badges

Collectives™ on Stack Overflow

mysql select query can not fetch data with null value

3 Answers 3

2 Comments

Solutions:

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Solutions:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related