CODEWITHSUNDEEP
mysqlsqlcount
sajad nabavi
sajad nabavi

Reputation: 79

MySQL logic count operation

I ran three SQL queries in MySQL, but there is a logic problem.

select count(*) from keeper where code!=''; -- result1=2893193
select count(*) from keeper where code=''; -- result2=66
select count(*) from keeper; -- result3=3481069

I expected that result1 + result2 = result3, but in fact, result1 + result2 < result3. Why is this?

Upvotes: 0

Views: 321

Answers (4)

user319198
user319198

Reputation:

Always use IS NuLL ans IS NOT NULL to get exact empty and non empty records respectively. it checks both empty and null values.

Try below:

select count(*) from keeper where code is NULL;

AND

select count(*) from keeper where code is NOT NULL

Alternatively you can use :

select  count(*) from keeper where LENGTH(COALESCE(code ,'')) = 0

will give you all records with an 'empty' value for code , treating NULL as empty.

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881543

1.   select count(*) from keeper where code!=''
2.   select count(*) from keeper where code=''
2.5. select count(*) from keeper where code is null
3.   select count(*) from keeper

Note the one inserted before 3. NULL is considered a separate case from any other value, being neither equal to, nor not equal to, any other value (including another NULL).

Upvotes: 0

octern
octern

Reputation: 4868

Three-valued logic attacks!

NULL and "" are two different things. NULL is considered to be neither equal nor not equal to "" and so neither of your queries will ever return it. I'd guess that the extra 500,000 records returned by your third query have code set to NULL. You can test for null fields using IS NULL or IS NOT NULL. If you do:

SELECT count(*) from keeper where code!='';
SELECT count(*) from keeper where code='';
SELECT count(*) from keeper where code IS NULL;

Those three results should add up to the total number of rows in your table.

Upvotes: 0

bretterer
bretterer

Reputation: 5781

Using IS NOT NULL AND IS NULL in addition to ='' will make sure you get all rows that are both just empty like you are looking for already or have the column set as NULL 

SELECT count(*) FROM keeper WHERE code!='' OR code IS NOT NULL;
SELECT count(*) FROM keeper WHERE code = '' OR code IS NULL

Upvotes: 3

Related Questions