MySQL GROUP_CONCAT vs. COALESCE regarding NULL values

Question

UPDATE

I just noticed that on the server the column values table3.noteare equal NULL, and on my local machine there are empty rows. After this embarrassing discovery, I conducted several tests, and everything works the same on both platforms.

And this is what they produce if I have two cells, and the second contains the 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" }

Thus, the first query ( COALESCE) retrieves NULLas empty rows, and the second query retrieves all values NULLfrom the result set. (This is unacceptable because I have many arrays and need to be synchronized.)

- . , GROUP_CONCAT NULL, .

, ( 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`

NULL s? ( , NULL, .)

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

( , ...)

, ( 1: n, table2 table3 table1). :

 //1st
 GROUP_CONCAT(COALESCE(`table3`.`note`, '') SEPARATOR ';') AS `table3_note`

 //2nd
 GROUP_CONCAT(`table3`.`note`) SEPARATOR ';') AS `table3_note`

, . 1- 2- ( var_dump()). , table3_note ( table3_id , ).

, ? , NULL, .

• http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce
• http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

- ?

• : MySQL Client API 5.1.44
• : API 5.0.51a

, COALESCE, ​​ , GROUP_CONCAT - - MySQL Client API?

, , . , . COALESCE, ? , for? ( , .)

---

. (IFNULL, IS NULL ..), , , , :

• GROUP_CONCAT NULL, NULL
• group_concat

: , . :

//another option for the query
IF(SUM(`table3`.`note` IS NULL) = 0, GROUP_CONCAT(`table3`.`note` SEPARATOR ';'), NULL) AS `table3_note`

//and another one...
ISNULL(GROUP_CONCAT(`table3`.`note` SEPARATOR ';'), '') AS `table3_note`

MySQL :

, NULL.

, COALESCE NULL, GROUP_CONCAT, ? . ?

Answer 1

, ( 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`

. , .