MySQL Query returns wrong result on NULL comparison and NULL Join

Question

So I have a query

SELECT count(*) from table where  cat='green' and state = state and product = product

Result: 15000

Now when I run the same query with an additional condition that should result to true

SELECT count(*) from table where cat='green' and state = state and product = product and lga = lga

Result: 0

Pls what on earth could be wrong??? I assume that lga=lga should result to true just like 1=1 results to true

EDIT: Table schema:

id int(11) NO PRI NULL auto_increment cat varchar(128) NO NULL
state varchar(8) YES NULL
lga varchar(20) YES NULL
product varchar(150) NO NULL

To add some more context, I have a page with filters, so the only way I can ensure that the filters are flexible is to implement it the way I did.

You might want to add sample data and also show us the actual query (what you pasted above won't even run). I'm not sure this is reproducible. — Tim Biegeleisen, Nov 25 '20 at 15:35
Show the table schema and some sample data that exhibit that problem... It makes no sense as currently written. Why would you compare a field to itself? That will always be true. So clearly you are not really doing that; maybe you are instead e.g. using a subquery and not realising what precedence aliases in different tables have. — underscore_d, Nov 25 '20 at 15:35
If there are all NULLs in lga, that would do it. Can't check nulls for equality. https://stackoverflow.com/questions/9608639/mysql-comparison-with-null-value — Martin Burch, Nov 25 '20 at 15:36
@MartinBurch is very correct. That is why Im having such results. You cannot check nulls for equality and the lga column contains null values for what I'm running. — Olamide226, Nov 25 '20 at 15:52

score 2 · Answer 1 · answered Nov 25 '20 at 16:46

If the lga fields for all records where cat = 'green' are all NULL, this will happen because NULL = NULL will filter out all records. "A NULL value is not equal to anything, even itself."

One approach I think you could take is to wrap the condition in ISNULL()

and IFNULL(lga,TRUE) = IFNULL('user provided input here',TRUE);

1 Answers1