3

I have below table in MySQL.

city_data
+------+-----------+-------------+
|  id  | city_code | city_name   |
+------+-----------+-------------+
| 4830 | BHR       | Bharatpur   |
| 4831 | KEP       | Nepalgunj   |
| 4833 | OHS       | Sohar       |
| 4834 | NULL      | Shirdi      |
+------+-----------+-------------+

and below query.

select id,city_code,city_name from city_data where city_code != 'BHR';

I was expecting 3 rows.

| 4831 | KEP       | Nepalgunj   |
| 4833 | OHS       | Sohar       |
| 4834 | NULL      | Shirdi      |
+------+-----------+-------------+

But getting only 2 rows.

| 4831 | KEP       | Nepalgunj   |
| 4833 | OHS       | Sohar       |
+------+-----------+-------------+

I am not able to understand why the row

| 4834 | NULL      | Shirdi      |

Not includes in the result of my query. The where condition(NULL != 'BHR') should have been passed.

Please, someone, help to clear the doubt.

shubham12511
  • 620
  • 1
  • 9
  • 25

3 Answers3

5

According to MySQL Reference Manual, section 3.3.4.6: Working with NULL values the following is why:

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

In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.

This means that NULL != 'BHR' will evaluate to NULL, which in turn will mean false to MySQL. In order for the query to work as you want, you have to append OR city_code IS NULL to your query.

Per Huss
  • 4,755
  • 12
  • 29
0

You cannot compare null values with !=, because it is null, use IS NULL predicate instead:

select id,city_code,city_name 
from city_data 
where city_code != 'BHR' OR city_code IS NULL;
0

It is not possible to test for NULL values with comparison operators, such as =, <, or <>. Therefore query is confusing and NULL record is being ignored. for more info go to https://www.w3schools.com/sql/sql_null_values.asp

R Hamilton
  • 263
  • 2
  • 14