CODEWITHSUNDEEP
mysqlgroup-concat
Sami
Sami

Reputation: 8419

Group_Concat in Concat not working with NULL values

I have a table

CREATE TABLE IF NOT EXISTS `dept` (
  `did` int(11) NOT NULL,
  `dname` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `dept` (`did`, `dname`) VALUES
(1, 'Hi'),
(2, NULL),
(3, 'Hello');

Then I have a query

select group_concat(concat(did,"','",dname) separator '),(') as Result from dept

It is producing result as 1','Hi'),('3','Hello

Question: How can I get result from above query as 1','Hi'),('2','NULL'),('3','Hello

It is missing the rows which have NULL values but I need to fetch all

Link for SQL Fiddle Demo of question

UPDATE: If I have more than one or all columns allowing NULL, Is there some way to apply COALESCE once for all or have to apply individually at each Column?

Upvotes: 8

Views: 12127

Answers (3)

John Woo
John Woo

Reputation: 263893

try this, use COALESCE

.., COALESCE(dname, 'NULL'),..

making it NULL string visible. SQLFIDDLE DEMO

Upvotes: 9

Michael Berkowski
Michael Berkowski

Reputation: 270767

From the MySQL aggregate function documentation:

Unless otherwise stated, group functions ignore NULL values.

Use COALESCE() to replace the nulls with a string, since they would be eliminated by the aggregate function. For example COALESCE(dbname, 'NULL') will return the string NULL if dbname IS NULL. Its purpose is to return the first non-null of the arguments you give it, and can therefore return a default value.

SELECT
  GROUP_CONCAT(CONCAT(did,"','", COALESCE(dname, 'NULL')) SEPARATOR "'),('") AS Result
FROM dept

Upvotes: 2

jsist
jsist

Reputation: 5253

Hope following query will serve your purpose

SELECT GROUP_CONCAT(
IF(dname IS NOT NULL, CONCAT(did,"','",dname), CONCAT(did,"','NULL")) 
SEPARATOR '),(') AS Result FROM dept

Upvotes: 0

Related Questions