MySQL and PostgreSQL: string concatenation with NULL

Who this is for: SQL learners who have used CONCAT or are moving a query between MySQL and PostgreSQL. You should know that NULL represents a missing or unknown value; see the SQL NULL lesson.

What you will learn: compare how CONCAT handles a NULL argument in two database systems, make the desired behavior explicit, and check database/version assumptions before porting a query.

Same function name, different NULL behavior

Consider combining two name parts when the second value is missing. MySQL 8.4 returns NULL if any argument passed to CONCAT is NULL:

SELECT CONCAT('Asha', NULL) AS display_name;

On the local MySQL server used for this example, the tested version was 8.4.3; the result was NULL.

PostgreSQL 18 documents that concat ignores NULL arguments, so the corresponding expression returns 'Asha':

SELECT concat('Asha', NULL) AS display_name;

Testing note: the MySQL result was executed on MySQL 8.4.3. PostgreSQL's documented behavior was checked against its official version 18 manual, but was not run against a PostgreSQL server in this environment. Treat the PostgreSQL result here as documentation-verified, not runtime-tested.

Choose the result you actually want

If a missing name part should simply be left out, CONCAT_WS joins non-NULL values with a separator. Both manuals document this behavior when the separator itself is not NULL:

SELECT CONCAT_WS(' ', 'Asha', NULL) AS display_name;

The MySQL 8.4.3 result was Asha. PostgreSQL 18's manual documents that concat_ws skips NULL value arguments too, but this was not runtime-tested against PostgreSQL here.

Another option is to decide explicitly what a missing value means with COALESCE. For example, replace a missing middle name with an empty string before combining it:

SELECT CONCAT(
    'Asha',
    ' ',
    COALESCE(NULL, '')
) AS display_name;

That intent is clearer than relying on a database's implicit NULL behavior. Adjust separators if your output should not contain extra spaces.

Before moving SQL between databases

• Check the documentation for the exact database and version you deploy.
• Test both ordinary values and NULL values; happy-path inputs do not reveal every portability issue.
• Write down the intended result for missing values, then choose a function or explicit COALESCE expression to match it.
• Do not assume that two databases treat same-named functions identically.

Sources and verification

See the official MySQL 8.4 string-functions reference and the PostgreSQL 18 string-functions reference. MySQL behavior was executed with version 8.4.3. PostgreSQL behavior is cited from its manual and remains pending a runtime check on PostgreSQL 18.