An SQL Annoyance

(Back in 2003 I ran a moderately popular tech blog on the Radio UserLand platform. This is an archived version of a post from that blog. You can view an index of all the archived posts.)

Monday, 8 December 2003

SQL isn't consistent under row/column transposition. For example:

select 3 + NULL

yields NULL. Yet,

create table TEMP ( NUM number );
insert into TEMP values ( 3 );
insert into TEMP values ( NULL );
select sum(NUM) from TEMP

yields 3 (since NULL valued rows are ignored by aggregate functions).

This inconsistency is all the more annoying since both:

select sum(NUM) from TEMP where NUM is not NULL


select sum(coalesce(NUM,0)) from TEMP

would yield the same result under an "aggregation of NULL is NULL" rule. Yet under the "aggregation function ignores NULL" rule, creating a single, efficient, cross-database query the yields NULL if there's a NULL row and the SUM otherwise is awkward at best.

This page was generated at 4:16 PM on 26 Feb 2018.
Copyright © 1999 - 2018 Rodney Waldhoff.