CONCAT With a NULL Argument Returns NULL on MySQL and Skips It on PostgreSQL

A customer list built with CONCAT(first_name, ' ', last_name) looks complete on PostgreSQL and SQLite. Moved to MySQL or MariaDB, the same query returns NULL for some customers.

A person without a last name comes out as 'Ann ' on SQLite and PostgreSQL and as NULL on MySQL and MariaDB, so a list of names quietly loses entries there. CONCAT_WS skips NULL arguments on all four engines.

What Each Engine Answered

On a table made with

CREATE TABLE people (first_name VARCHAR(20), last_name VARCHAR(20));
INSERT INTO people VALUES ('Ann', NULL);

the example

SELECT CONCAT(first_name, ' ', last_name) FROM people;

gave these answers:

These answers need SQLite 3.44 or later.

What to Write Instead

This form answers the same on every engine:

SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) FROM people;

Wrap every argument that can be NULL in COALESCE(x, '').

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:3:8: warning: CONCAT skips NULL arguments on SQLite and PostgreSQL. On MySQL and MariaDB one NULL makes the whole result NULL. [concat-null]
    CONCAT(first_name, ' ', last_name)
    Wrap every argument that can be NULL in COALESCE(x, '').

1 problem: 1 warning.

baresql rules concat-null prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.