AP 08 - Beware of NULL with arithmetic and string operations

Severity

Type

Problem

Using arithmetic and string operations on NULLable columns could have side effects that you didn’t plan for.

Table with sample data:

We concatenate the first_name, middle_name and last_name to illustrate the anti pattern for string operations.

SELECT customer_id
      ,CONCAT(first_name
          ,middle_name
          ,last_name) AS full_name
  FROM customer

As the middle_name is unknown for customer_id 2 the concat string operation also results in an unknown value (NULL). We might have expected “edgar uelzenguelz” as the full name for that customer_id

Solution

We recommend using a defensive programming style when writing SQL queries. As a rule, you should always assume any column can be NULL at any point. It’s a good idea to provide a default value for that column. This way you make sure that even if your data becomes NULL the query will work.

For the string operation we can use the COALESCE function to achieve our objective

SELECT customer_id
      ,CONCAT(first_name
          ,' '
          ,COALESCE(middle_name
              ,'')
          ,' '
          ,last_name) AS full_name
  FROM customer

If you want to be very careful, you can use COALESCE for all of the columns in the string concatenation, not just the ones that can be NULL. This will make sure that your code will still work even if the NULLability of a column changes.

SELECT customer_id
      ,CONCAT(first_name
          ,' '
          ,COALESCE(middle_name
              ,'')
          ,' '
          ,last_name) AS full_name
  FROM customer

Legitimate case

The columns used in the expression are NOT NULLable.