Monday, July 11, 2005

MySQL Coalesce example

Example: You have a relational model where one table serves as a lookup-table (tblDefault) with default values for another table (tblInstance). tblInstance contains NULL-values in the cells where defaults are to be retrieved from tblDefault. The following query will do the merging:

SELECT i.id, i.name, i.whatever,
COALESCE(i.specific1, d.default1) as val1,
COALESCE(i.specific2, d.default2) as val2
FROM tblInstance i
LEFT OUTER JOIN tblDefault d
ON i.foreignKey = d.primaryKey;


COALESCE selects the first non-null value of its arguments, and the left outer join makes sure all records from the left table are returned.

Oh, and whitespace matters in this case. Placing a space between COALESCE and ( produces a general syntax error.

27 Comments:

At 6:28 pm, Anonymous Anonymous said...

thanks for the example, helped a newbie out :)

 
At 6:29 pm, Blogger ballerina said...

Great to hear, that's what I was hoping. :-)

 
At 1:46 pm, Anonymous Anonymous said...

Laila you are gorgeous!! and the reference that the COALESCE() and that no whitespace is to exist between the COALESC and the brackets worked a treat..

Muchly Appreciated.

Cheers
Corporate007 (From the land DownUnder)

 
At 2:13 pm, Blogger ballerina said...

Thank you both for feedback! This post is the most googled on the blog, and I always wonder if it actually helps anybody out. :-)

 
At 10:39 pm, Blogger Carl said...

Thank you! I'm just getting introduced to the COALESCE function, and ran into the whitespace problem right off the bat; yours is the first documentation I've found of that peculiarity.

 
At 3:59 am, Anonymous Anonymous said...

Awesome! I was searching for coalesce info but actually the outer join was what I needed.

I always wondered what exactly outer join was used for. Together with coalesce this is a great example. You saved me so much time!

 
At 9:12 pm, Anonymous Anonymous said...

Thanks.

You can nest them, like so:

SELECT i.id, i.name, i.whatever,
COALESCE(COALESCE(i.specific1, d.default1),'no value' as val1,
COALESCE(i.specific2, d.default2) as val2
FROM tblInstance i
LEFT OUTER JOIN tblDefault d
ON i.foreignKey = d.primaryKey;

 
At 10:58 am, Anonymous Anonymous said...

no real need to nest them in that example though. this produces the same result and is likely more efficient :

SELECT i.id, i.name, i.whatever,
COALESCE(i.specific1, d.default1,'no value' as val1,
COALESCE(i.specific2, d.default2) as val2
FROM tblInstance i
LEFT OUTER JOIN tblDefault d
ON i.foreignKey = d.primaryKey;

 
At 10:08 pm, Blogger TimH said...

just so you know, you helped a Norwegian from Bergen in the US years later (my grandmother was from Bergen, and she said she had 18 brothers and sisters when they started on the boat to the US and only 15 when it arrived)
the white space thing saved me just now! had no idea why I was getting an error that "function COALESCE does not exist

 
At 7:48 pm, Anonymous Anonymous said...

Also a newbie... what worked for me was as follows:

SELECT i.id, i.name, i.whatever,
COALESCE(i.specific1, 0) as val1,
COALESCE(i.specific2, 0) as val2
FROM tblInstance i;

... and thanks for the great example!

 
At 12:40 am, Anonymous Anonymous said...

Thanks for the blog! It helped me today :)

 
At 3:39 pm, Anonymous Anonymous said...

Joao You eat a ")" in Your comment:)

Correct version:

SELECT i.id, i.name, i.whatever,
COALESCE(i.specific1, d.default1,'no value') as val1,
COALESCE(i.specific2, d.default2) as val2
FROM tblInstance i
LEFT OUTER JOIN tblDefault d
ON i.foreignKey = d.primaryKey;

Thanks

 
At 4:22 pm, Blogger Kevin S. Gareau said...

Thanks very much, as with others, the whitespace/syntax error was hosing me big time.

 
At 6:15 pm, Anonymous Anonymous said...

hubba hubba

 
At 9:02 am, Anonymous Anonymous said...

Another interesting usage of COALESCE() function is to calculate average value of data stored in one row - if we allow to store NULL values there.

When we have this table (three items per row):
CREATE TABLE IF NOT EXISTS `voting` (
`opinion_id` mediumint(9) unsigned NOT NULL auto_increment,
`rank_0` tinyint(4) default NULL,
`rank_1` tinyint(4) default NULL,
`rank_2` tinyint(4) default NULL,
PRIMARY KEY (`opinion_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `voting` VALUES (1, 1, 2, NULL);
INSERT INTO `voting` VALUES (2, 3, NULL, 4);
INSERT INTO `voting` VALUES (3, 3, 1, 2);

We can use this SELECT statement to get average values stored in a row:

SELECT *,
(COALESCE(V.rank_0, 0)
+ COALESCE(V.rank_1, 0)
+ COALESCE(V.rank_2, 0))
/
(3 -
(COALESCE(V.rank_0 - V.rank_0, 1)
+ COALESCE(V.rank_1 - V.rank_1, 1)
+ COALESCE(V.rank_2 - V.rank_2, 1))
) AS row_avg FROM voting V


I talk about it in more detail on my blog post at How to calculate average value of data in a row

 
At 6:57 am, Anonymous Lex said...

:D Just what i needed!
Thanks

 
At 8:13 pm, Blogger Roxana said...

really helpful. thanks.

 
At 11:47 am, Blogger Unknown said...

Great this is also available in MySQL.

Very helpfull

 
At 9:06 am, Anonymous Javier said...

It was helpfull to me. I had a Group_concat joining several columns where some columns could have null values. In that case Group_concat returned null instead of those values that were not null. Example: in a row col1='text' and col2=NULL

GROUP_CONCAT(col1,' ',col2) -> returns null

GROUP_CONCAT(COALESCE(col1,''),' ',COALESCE(col2,''))
returns ' text'

 
At 11:46 am, Blogger Unknown said...

I have comma saparated values in one table and I want corresponding name of that id from another table...
i.e. contact table contains companyids(1,2),so how to get comanyname(comp1,comp2) from company table

please help
thanks in advance

 
At 6:47 pm, Anonymous Anonymous said...

if you're using just two columns then

IFNULL(col1, col2) is the same as COALESCE(col1, col2)

and if you want to be sure your first one is not a blank string then

IFNULL(NULLIF(col1,''), col2)

if you want to be sure that none of the columns to be selected by COALESCE are empty, default, or blank, you can use NULLIF in COALESCE like so:

COALESCE( NULLIF(str1,'') , NULLIF(int2,0) , col3 , 'nothing')

 
At 12:14 am, Anonymous Donn Lee said...

Just the SQL example I was needing. The manual didn't really show Coalesce in the context of a full query. Perfect. Thanks!

 
At 1:34 pm, Anonymous Anonymous said...

THANKS

 
At 7:18 am, Anonymous cheap seo services said...

Thanx Great tuto

 
At 7:19 am, Anonymous cheap seo services said...

thanx,its a great tutorial

 
At 10:17 am, Anonymous Anonymous said...

thanks laila

 
At 10:20 am, Anonymous vandna thakur said...

Thanks for example

 

Post a Comment

<< Home

En blogg kan være et godt verktøy for en som i litt for stor grad glemmer de små og store tingene som utgjør livet. Dette er min reserve- hukommelse.