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?
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
IS NULL return values that are just empty which is why I always put both. It is not needed really with the IS NOT NULLThree-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.
NULL and "" are two different things - unless you have a brain-dead DBMS like Oracle which can't tell the difference :-) Sorry, can never miss an opportunity to bag their bizarre decision back in the day.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).
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.