MySQL GROUP_CONCAT vs. COALESCE concerning NULL values

Question

UPDATE I just noticed that in the server the column `table3.note` values are `NULL` and on my local machine they are empty strings. After this embarassing discovery I made some testing and everything works the same on both platforms. And this is what they produce if I have two cells and the second one contains an actual value (the first is `NULL`): ``` //1st GROUP_CONCAT(COALESCE(`table3`.`note`, '') SEPARATOR ';') AS `table3_note` //var_dump(): array(2) { [0]=> string(0) "" [1]=> string(4) "Test" } //2nd GROUP_CONCAT(`table3`.`note`) SEPARATOR ';') AS `table3_note` //var_dump(): array(1) { [0]=> string(4) "Test" } ``` So the 1st query (`COALESCE`) retrieves `NULL`s as empty strings and the 2nd strips all `NULL` values from the result set. (This is unacceptable because I have many arrays and they need to be synchronized.) The original problem is solved because of my mistake. I would still like to know why `GROUP_CONCAT` ignores `NULL`s even if checked. Here's the query that works prop…

Accepted Answer

Here's the query that works properly (doesn't strip the `NULL`s):

```
SELECT `table1`.*
  GROUP_CONCAT(COALESCE(`table3`.`id`, '') SEPARATOR ';') AS `t3_id`,
  GROUP_CONCAT(COALESCE(`table3`.`note`, '') SEPARATOR ';') AS `t3_note`,
  FROM `table1`
    LEFT JOIN `table3` ON `table3`.`id` = `table1`.`id`
      GROUP BY `table1`.`id`
```

Original question is left intact. It's quite messy and difficult to understand but the query above works for me.

MySQL GROUP_CONCAT vs. COALESCE concerning NULL values

Solution

Problem

Related problems