Why MySQL GROUP_CONCAT returns NULL when there are some non-NULL values

Here is some sample code:

CREATE TABLE test (
  first_name VARCHAR(255),
  last_name VARCHAR(255)
);
INSERT INTO test (first_name) VALUES ('first_1');
INSERT INTO test (last_name) VALUES ('last_1');

SELECT GROUP_CONCAT(first_name, last_name) FROM test;
SELECT GROUP_CONCAT(first_name), GROUP_CONCAT(last_name) FROM test;

The first select returns null, while the MySQL documentation states that it returns null if there are no non-NULL values. Here is a demo.

Solution

This result is expected. Consider the first query:

SELECT GROUP_CONCAT(first_name, last_name) FROM test;

Because each record has a NULL for either the first or last name, you are concatenating a NULL value and then aggregating on that NULL, also yielding NULL. To understand this behavior better, run the query SELECT CONCAT('Hello', NULL) FROM dual and observe that the output is NULL.

However, in the second query:

SELECT GROUP_CONCAT(first_name), GROUP_CONCAT(last_name) FROM test;

You are group concatenating on the individual columns. In this case, the NULL values will be ignored, and you are left with the non NULL first and last name individual values.

Diagrammatically, we can draw the following:

first_name | last_name | CONCAT (first_name, last_name)
first_1    | NULL      | NULL
NULL       | last_1    | NULL
--------------------------------
first_1    | last_1    | NULL    <-- GROUP_CONCAT of columns

You can see that GROUP_CONCAT across records behaves like the other aggregate functions, ignoring NULL values. But across columns, GROUP_CONCAT will do a concatenation first, resulting in NULL if even one value be NULL.