When it comes to concatenating lists in DuckDB, we can use the list_concat() function (and its aliases), or the list concatenation operator (||). Often when we have a choice like this, we can use either option interchangeably. But that isn’t quite true in this case.

There’s an important difference between list_concat() and the || operator in DuckDB, and it all comes down to how it handles NULL values.

The Difference

The list_concat() function and the || operator handle NULL lists differently:

When one list is NULL:

• list_concat(): Returns the non-NULL list.
• ||: Returns NULL.

When both lists are NULL:

• list_concat(): Returns an empty string.
• ||: Returns NULL.

Examples

Concatenate a Non-NULL List with a NULL List

Here’s an example to demonstrate what happens when we try to concatenate a non-NULL list with a NULL list:

.nullvalue 'null'
SELECT 
    list_concat(['Scratch', NULL, 'Purr'], NULL) AS list_concat,
    ['Scratch', NULL, 'Purr'] || NULL AS concatenation_operator;

Result:

+-----------------------+------------------------+
|      list_concat      | concatenation_operator |
+-----------------------+------------------------+
| [Scratch, NULL, Purr] | null                   |
+-----------------------+------------------------+

Both options were trying to concatenate a non-NULL list with a NULL list. The list_concat() function returned the non-NULL list but the concatenation operator simply returned NULL.

I also used the .nullvalue dot-command in my DuckDB CLI to set NULL values to return ‘null’ instead of the default empty string.

Concatenate Two NULL Lists

Here’s what happens when we concatenate two NULL lists:

.nullvalue 'null'
SELECT 
    list_concat(NULL, NULL) AS list_concat,
    NULL || NULL AS concatenation_operator;

Result:

+-------------+------------------------+
| list_concat | concatenation_operator |
+-------------+------------------------+
|             | null                   |
+-------------+------------------------+

The list_concat() function returned an empty string while the || operator returned a NULL value.

LIST_CONCAT()‘s Aliases

list_concat() has a bunch of aliases. All of these behave the same way, including how they handle NULL input.

SELECT 
    list_concat(NULL, NULL) AS list_concat,
    list_cat(NULL, NULL) AS list_cat,
    array_concat(NULL, NULL) AS array_concat,
    array_cat(NULL, NULL) AS array_cat;

Result:

+-------------+----------+--------------+-----------+
| list_concat | list_cat | array_concat | array_cat |
+-------------+----------+--------------+-----------+
|             |          |              |           |
+-------------+----------+--------------+-----------+