Read more

Don't sum up columns with + in a SQL query if NULL-values can be present.

Natalie Zeumann
March 15, 2018Software engineer at makandra GmbH

Don't sum up columns with + in a sql-query if NULL-Values can be present.

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

MySQL and PostgreSQL cannot sum up NULL values with the + value. The sum value will be NULL.

MySQL:

mysql> select 1 + 2 + 3;
+-----------+
| 1 + 2 + 3 |
+-----------+
|         6 |
+-----------+
1 row in set (0,00 sec)

mysql> select 1 + NULL + 3;
+--------------+
| 1 + NULL + 3 |
+--------------+
|         NULL |
+--------------+
1 row in set (0,00 sec)

Postgres:


test_database=# select 1 + 2 + 3;

 ?column? 
----------
        6
(1 row)

test_database=# select 1 + NULL + 3;

 ?column? 
----------
         
(1 row)

If you want to do additions in the database:

  • use SUM if it's an option to sum up a column of a result set instead of expressions (SUM ignores NULL values)
  • wrap columns with: COALESCE(column, 0)(COALESCE takes the first non-null argument)
Posted by Natalie Zeumann to makandra dev (2018-03-15 16:31)