Cargo Cult Programming with COALESCE

I notice one query that do this:

select sum(coalesce(qty,0)) from sample;

Yes, this would result to null:

 select 1 + 2 + null as total;

However, like with most things in life, NULL interpretation in SQL is not symmetrical, given the values above and materializing it to rows:

insert into sample(item,qty) values('keyboard',1); insert into sample(item,qty) values('mouse',2); insert into sample(item,qty) values('cpu',null);

This query would not yield null:

select sum(qty) as total from sample;

That would result to sum of 3, not null.

And given a table with no rows, which of the following would report a value of 0 when there's no rows?

create table table_with_no_row ( item varchar(50) not null, qty int ); select sum( coalesce(qty, 0) ) as grand_total from table_with_no_row; select coalesce( sum(qty), 0 ) as grand_total from table_with_no_row;

Surprise! Ok not so surprising to some, it's the latter that will yield 0. The first one will yield null, despite having coalesce too

Another good use of coalesce:

select h.order_id, h.order_date, coalesce(sum(d.qty),0) as total from order_header h left join order_detail d on d.order_id = h.order_id group by h.order_id     ,h.order_date; select h.order_id, h.order_date, sum(coalesce(d.qty,0)) as total from order_header h left join order_detail d on d.order_id = h.order_id group by h.order_id     ,h.order_date;

Both queries above would yield the same result.

order_id    order_date total ----------- ---------- ----------- 1           1940-10-09 2 2           1956-10-28 0 (2 row(s) affected)

Given they have the same result, why should we opt for sum( coalesce(d.qty,0) )?

Aggregate functions automatically discard null values, hence it's superfluous to put a coalesce inside an aggregate function. Placing a coalesce inside an aggregate hurts performance, as coalesce is repeatedly executed on every row. I once optimized a very slow query just by removing the COALESCE inside of SUM and placing it outside of SUM, way back long time ago since the dawn of time

Cargo Cult Programming is rearing its ugly head. Cargo Cult Programming is wreaking havoc on database performance

Happy Computing! ツ

Insisting ELSE 0 on COUNT aggregate? You might as well substitute your birthday

Some unenlightened SQL user from stackoverflow wanted to do this, note the ELSE 0 part:

select count(case when 'blah' = 'bleh' then 1 else 0 end) 
from information_schema.tables

Due to his insistence on the need for ELSE 0 part, I recommended to him that he can substitute his birthday instead on that ELSE part, it "works" the same anyway as the ELSE 0 approach:

select 
count(case when 'blah' = 'bleh' then 1 else 'April 20, 1939' end) 
from information_schema.tables

Of course, both queries above are a crime against humanity, and I cannot let this wrong deed goes unpunished uncorrected, here's the correct query:

Idiomatic MySQL (duality between boolean and integer) :

select sum('blah' = 'bleh') 
from information_schema.tables

Idiomatic Postgresql:

select sum(('blah' = 'bleh')::int) 
from information_schema.tables

SQL Server:

select sum(case when 'blah' = 'bleh' then 1 end) 
from information_schema.tables

We can also use these, but the above works well, free of noise and devoid of Cargo Cult Programming, it's better to use that code. SUM ignores 0 and NULLs, why include them?

select sum(case when 'blah' = 'bleh' then 1 else 0 end) 
from information_schema.tables;

select sum(case when 'blah' = 'bleh' then 1 else null end) 
from information_schema.tables;

But for the love of our craft, don't do this (the ELSE 0 on COUNT), this is very wrong :

select count(case when 'blah' = 'bleh' then 1 else 0 end) 
from information_schema.tables

If you insist so, re-read from the top