Can't receive results where field value is 0 or NULL

Question

How to receive results from mysql table where field value is NOT 0 or NULL? If I write condition field!=1, it gives nothing, but there are a lot of fields where field!=1. Why?

UPDATE:

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `somevar` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `somevar`)
VALUES
    (1,1),
    (2,0),
    (3,NULL),
    (4,NULL),
    (5,0);

This query

SELECT * FROM `test` WHERE `somevar`!=1

gives back only where somevar=0, but not NUL. how to echo all results where somevar!=1?

post your whole code, please. you should be able to select rows with `NULL` values. And try `WHERE field IS NULL` — RealCheeseLord, Aug 09 '17 at 15:05
if we assume somevar is null, 0 or 1 then you could use coalesce. `WHERE Coalesce('somevar',0)!=1` but the null safe operator seems like the best solution. I generally forget about the null safe operator. — xQbert, Aug 09 '17 at 15:22
Possible duplicate of [MySQL comparison with null value](https://stackoverflow.com/questions/9608639/mysql-comparison-with-null-value) — Nuri Tasdemir, Aug 09 '17 at 15:23

wchiquito · Answer 1 · 2017-08-09T15:34:38.170

You can use NULL-safe equal to operator:

mysql> DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `test` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `somevar` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `test`
    ->   (`somevar`)
    -> VALUES
    ->   (1), (0),
    ->   (NULL), (NULL),
    ->   (0);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT `id`, `somevar`
    -> FROM `test`
    -> WHERE NOT `somevar` <=> 1;
+----+---------+
| id | somevar |
+----+---------+
|  2 |       0 |
|  3 |    NULL |
|  4 |    NULL |
|  5 |       0 |
+----+---------+
4 rows in set (0.00 sec)

See db-fiddle.

score 1 · Answer 2 · answered Aug 09 '17 at 15:19

1

You can use somevar IS NULL. For mysql NULL is unknown value, therefore NULL != 1 is NULL instead of true. For detailed information you can look at mysql documentation

SELECT * FROM `test` WHERE `somevar` IS NULL OR `somevar`!=1

answered Aug 09 '17 at 15:19

Nuri Tasdemir

9,720
3
42
67

score 1 · Accepted Answer · answered Aug 09 '17 at 15:19

This has been answered many times on this site. NULL Is neither equal nor not equal to anything else. It cannot be compared in traditional ways. You have to ask for NULL values explicitly.

SELECT * FROM `test` WHERE `somevar`!=1 OR `somevar` IS NULL

Can't receive results where field value is 0 or NULL

3 Answers3