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! ツ