SQL (MariaDB) NULL value is ignored

Question

In MariaDB' s client mycli I am trying to SELECT items whose value vend_country is either NULLor MI so I wrote this simple query:

SELECT * 
FROM vendors
WHERE vend_state IN (NULL,'MI');

And I got this output:

+-----------+-------------+-----------------+-------------+--------------+------------+----------------+
|   vend_id | vend_name   | vend_address    | vend_city   | vend_state   |   vend_zip | vend_country   |
|-----------+-------------+-----------------+-------------+--------------+------------+----------------|
|      1001 | Anvils R Us | 123 Main Street | Southfield  | MI           |      48075 | USA            |
+-----------+-------------+-----------------+-------------+--------------+------------+----------------+

Which is weird, because it seems to be missing the rows with NULL value that are found in the same table. This query demonstrates this:

SELECT * 
FROM vendors;

The output clearly shows two rows with null value which should be outputted with first query:

+-----------+----------------+-----------------+-------------+--------------+------------+----------------+
|   vend_id | vend_name      | vend_address    | vend_city   | vend_state   | vend_zip   | vend_country   |
|-----------+----------------+-----------------+-------------+--------------+------------+----------------|
|      1001 | Anvils R Us    | 123 Main Street | Southfield  | MI           | 48075      | USA            |
|      1002 | LT Supplies    | 500 Park Street | Anytown     | OH           | 44333      | USA            |
|      1003 | ACME           | 555 High Street | Los Angeles | CA           | 90046      | USA            |
|      1004 | Furball Inc.   | 1000 5th Avenue | New York    | NY           | 11111      | USA            |
|      1005 | Jet Set        | 42 Galaxy Road  | London      | <null>       | N16 6PS    | England        |
|      1006 | Jouets Et Ours | 1 Rue Amusement | Paris       | <null>       | 45678      | France         |
+-----------+----------------+-----------------+-------------+--------------+------------+----------------+

I also tried some other queries which also ignore rows with NULL value:

mariadb root@localhost:crashcourse>  SELECT * 
                                 ->  FROM vendors
                                 ->  WHERE vend_state IN ('<null>','MI');
+-----------+-------------+-----------------+-------------+--------------+------------+----------------+
|   vend_id | vend_name   | vend_address    | vend_city   | vend_state   |   vend_zip | vend_country   |
|-----------+-------------+-----------------+-------------+--------------+------------+----------------|
|      1001 | Anvils R Us | 123 Main Street | Southfield  | MI           |      48075 | USA            |
+-----------+-------------+-----------------+-------------+--------------+------------+----------------+


mariadb root@localhost:crashcourse>  SELECT * 
                                 ->  FROM vendors
                                 ->  WHERE vend_state IN ('NULL','MI');
+-----------+-------------+-----------------+-------------+--------------+------------+----------------+
|   vend_id | vend_name   | vend_address    | vend_city   | vend_state   |   vend_zip | vend_country   |
|-----------+-------------+-----------------+-------------+--------------+------------+----------------|
|      1001 | Anvils R Us | 123 Main Street | Southfield  | MI           |      48075 | USA            |
+-----------+-------------+-----------------+-------------+--------------+------------+----------------+

On the other hand this works completely fine, so IN keyword must be working:

SELECT * 
FROM vendors
WHERE vend_id IN (1001,1005);

+-----------+-------------+-----------------+-------------+--------------+------------+----------------+
|   vend_id | vend_name   | vend_address    | vend_city   | vend_state   | vend_zip   | vend_country   |
|-----------+-------------+-----------------+-------------+--------------+------------+----------------|
|      1001 | Anvils R Us | 123 Main Street | Southfield  | MI           | 48075      | USA            |
|      1005 | Jet Set     | 42 Galaxy Road  | London      | <null>       | N16 6PS    | England        |
+-----------+-------------+-----------------+-------------+--------------+------------+----------------+

So can anyone tell me, how can I search for NULL value using IN keyword?

score 1 · Accepted Answer · answered May 16 '17 at 20:09

1

You can't use NULL quite like that with IN().

Your two options are:

Separate your NULL filter:

SELECT * 
FROM vendors
WHERE vend_state IN ('MI','etc')
OR vend_state IS NULL;

Or use ISNULL()

SELECT * 
FROM vendors
WHERE ISNULL(vend_state,'XX') IN ('XX','MI');

answered May 16 '17 at 20:09

Aaron Dietz

10,137
1
14
26

1

Well I don't like this fact... I just started learning SQL and SQL already seems weird. – 71GA May 16 '17 at 20:16
1

@71GA Yeah, it doesn't make perfect sense at first but will eventually. Basically.. `IN()` expects *values*, and `NULL` is the *absence of a value*. Therefore they are incompatible. You also cannot say `vend_state = NULL`, because something cannot *equal* the absence of something. Hence we use `vend_state IS NULL`. The only way to get around this, and use comparison operators with `NULL`, is to replace `NULL` with an actual value. For example by using `ISNULL(vend_state,'XX')` to assign the value 'XX' where `vend_state IS NULL`. – Aaron Dietz May 16 '17 at 20:30
Ok it makes sense if I look at it from this perspective. So `NULL` means "*no value*" and that's why this problem arises. – 71GA May 21 '17 at 05:17

score 0 · Answer 2 · answered May 16 '17 at 20:08

0

You cannot do a comparison to NULL this way. You need to do an explicit, separate comparison:

SELECT * 
FROM vendors
WHERE vend_state IS NULL OR vend_state = 'MI';

answered May 16 '17 at 20:08

Gordon Linoff

1,242,037
58
646
786

SQL (MariaDB) NULL value is ignored

2 Answers2