CODEWITHSUNDEEP
mysqlnullgroup-by
RP-
RP-

Reputation: 5837

mysql ignoring NULL while using group by and where condition

I am able to replicate my issue with a very simple case..
Explanation
I have a very simple table my_table with one column column1. 

create table my_table (column1 varchar(58));

I have few values for this column, NULL is also one of them.

insert into my_table (column1) values ('value1'), ('value1'), ('value2'), (null), ('value2');

Problem
When I try to query for group by column1 It is giving expected results by grouping all NULLs together. However if I add a where clause on column1 something like 

select count(1) as value_count, column1 from my_table where column1 <> 'value1' group by column1;

It is ignoring both value1 and NULL where I was expecting to ignore only value1.

With this simple case I could get a workaround for this by adding an OR condition, But it is a real pain to add this condition all over in my original case.

Could someone can explain me better why this behavior and how can I fix this?

Upvotes: 2

Views: 1572

Answers (3)

user359040
user359040

Reputation:

This is because any comparison with a NULL does not produce a true or false result, but instead produces a NULL result. Consequently, the condition column1 <> 'value1' evaluates as NULL where column1 is NULL, and so NULL values are not selected.

You can get around this by using a function such as coalesce to test column1 - like so:

select count(1) as value_count, column1 
from my_table 
where coalesce(column1,'') <> 'value1' 
group by column1;

Upvotes: 3

Ben
Ben

Reputation: 35613

Null means "a value, but I don't know what it is".

So is column1 <> 'value1'? If column1 is null, then "Is a value, but I don't know what it is, unequal to 'value1'"?

Clearly the answer is "I don't know. I don't know what the value is".

The only rows included by a where clause are those which pass the where clause test. We don't know if this row passes the test, so it will not be included in the query.

Upvotes: 3

Tom Mac
Tom Mac

Reputation: 9853

You could try making use of the MySQL IFNULL in your predicate:

select count(1) as value_count, column1 from my_table where ifnull(column1,'nullvalue') <> 'value1' group by column1;

Upvotes: 1

Related Questions