Exclude rows with null values

Question

I'm doing a bunch of sum queries:

SELECT col1 + col2 + col3 + ...

Some of the values in some of the columns are null. I'm checking for them by doing:

SELECT CASE WHEN col1 is not null and col2 is not null and ...

I wonder if there is a more concise syntax for accomplishing this task.

score 10 · Accepted Answer · 2012-03-12T19:30:35.640

10

Well, since the sum of any number and null is null, you can do something like the following (with the obvious ... filled in):

select big_honking_sum
from(
    select col1+col2+col3+...+coln as big_honking_sum
    from some_table
)sum
where big_honking_sum is not null;

edited Mar 12 '12 at 19:30

answered Mar 12 '12 at 19:22

Ohh, thanks. I had it in my head that 1 + null equaled 1. I guess I've been programming too much javascript. – canisrufus Mar 12 '12 at 19:26
@canisrufus - You're most welcome. If you found my answer helpful, please consider accepting it by clicking on the check mark below the upvote/downvote arrows next to my answer. – Mar 12 '12 at 19:31
1

;) SO won't let me select an answer for the first fifteen minutes. – canisrufus Mar 12 '12 at 19:34

John P · Answer 2 · 2012-03-13T07:16:25.927

6

Use COALESCE function if you can accept that a NULL value will be treated as 0.

SELECT COALESCE(Col1,0)
      + COALESCE(Col2,0)
      + COALESCE(Col3,0)
      AS TOTAL FROM table;

Perhaps you could consider using 0 as default value instead of NULL if applicable.

edited Mar 13 '12 at 07:16

answered Mar 12 '12 at 19:25

John P

Thanks for the advice. In my case, null is for unspecified values, where zero is for zero values. – canisrufus Mar 12 '12 at 19:31

Erwin Brandstetter · Answer 3 · 2023-04-01T22:51:46.897

Much simpler, faster, clearer:

... WHERE (col1, col2, col3) IS NOT NULL;

The expression returns true if (and only if) all fields in the ROW expression are NOT NULL. See:

Can be applied to the whole row, too:

SELECT ...
FROM   my_table t
WHERE  (t.*) IS NOT NULL;

Postgres even has a dedicated function for the purpose: num_nulls(). Slightly more expensive in my tests on Postgres 15, but clearer:

... WHERE num_nulls(col1, col2, col3) = 0;

3 Answers3