user2432443 user2432443 - 2 years ago 93
SQL Question

mysql select query can not fetch data with null value

enter image description here

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download