NULL values in where condition is not working

Question

I have encountered a weird problem in a MySQL query. This following query is returning me 0 result whereas there are many rows which should be returned. the query is

SELECT DISTINCT SKU, EAN, URL, CID 
  FROM tab_comp_data 
 WHERE status =''

And in many rows there are NULL values in STATUS column but it returns me no rows.

I have tried some other way round.

SELECT DISTINCT SKU, EAN, URL, CID 
  FROM tab_comp_data 
 WHERE status <>'INACT'

this also returns me no rows.

P.S. the STATUS column can only be NULL or 'INACT'

This is the best question ever to get a thousan replies in less tha 10 seconds — Oscar Pérez, May 15 '15 at 12:10
http://stackoverflow.com/questions/24444518/difference-between-null-and-blank-value-in-mysql may help — xQbert, May 15 '15 at 12:12

score 2 · Accepted Answer · answered May 15 '15 at 12:10

2

I you want to query for NULL values, you should use the IS NULL operator.

So, your query should look like:

SELECT DISTINCT SKU, EAN, URL, CID 
  FROM tab_comp_data
 WHERE status IS NULL

answered May 15 '15 at 12:10

Oscar Pérez

4,377
1
17
36

score 1 · Answer 2 · answered May 15 '15 at 12:09

1

if the value of status is null you have to use is null. That is different from =''

select DISTINCT SKU, EAN, URL, CID from tab_comp_data WHERE status is null

answered May 15 '15 at 12:09

Jens

67,715
15
98
113

score 1 · Answer 3 · answered May 15 '15 at 12:09

null is not a value - it's a lack thereof. As such, it cannot be evaluated with the equality (=) or inequality (<>) operators. Instead, it should be checked explicitly with the is operator:

SELECT DISTINCT sku, ean, url, cid 
FROM   tab_comp_data 
WHERE  status IS NULL

score 0 · Answer 4 · answered May 15 '15 at 12:10

0

Try to use is NULL

select DISTINCT SKU, EAN, URL, CID from tab_comp_data WHERE status is NULL

answered May 15 '15 at 12:10

Rahul Tripathi

168,305
31
280
331

score 0 · Answer 5 · answered May 15 '15 at 12:44

0

SELECT DISTINCT SKU, EAN, URL, CID 
  FROM tab_comp_data 
 WHERE status IS NULL

Use is null , instead of =''

answered May 15 '15 at 12:44

Vidhi

397
1
5
17

NULL values in where condition is not working

5 Answers5