MYSQL: How to append Null value using GROUP_CONCAT and Separator using groupby?

Question

How to display Null using Group Concat and Separator

select sr.aID,
 GROUP_CONCAT(p.GENDER SEPARATOR ',') as Gender,
 GROUP_CONCAT(sr.FANS SEPARATOR ',') as FA,
from RELATION sr, Person p where RELATION _TYPE = 'FATHER'
and  sr.bID= p.ID group by sr.subjektA_ID;

It returns following value for FA

1 | MALE, FEMALE | YES, NO
2 | FEMALE, MALE | NO, YES
3 | FEMALE, MALE | YES
4 | FEMALE, MALE | NO

I want it to display Null value i.e

3 | FEMALE, MALE | YES, NULL
4 | FEMALE, MALE | NULL, NO

How to achieve same using GROUP Concat and Separator along together

Answer 1

Try replacing those NULL values with the string literal 'NULL':

SELECT
    sr.aID,
    GROUP_CONCAT(p.GENDER) AS Gender,
    GROUP_CONCAT(COALESCE(sr.FANS, 'NULL')) AS FA
FROM RELATION sr
INNER JOIN Person p
    ON sr.bID = p.ID
WHERE
    RELATION _TYPE = 'FATHER'
GROUP BY
    sr.aID;

GROUP_CONCAT, like most of the aggregate functions, ignore NULL values. But for presentation purposes, you only want to see the string 'NULL', so the above suggestion should work.

Note I also replaced your old style joins with explicit joins. Also, you should select the same column which you used to aggregate.