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.
posted by Laila @ 10:31 AM
17 Comments:
thanks for the example, helped a newbie out :)
Great to hear, that's what I was hoping. :-)
Thanks laila - that's really helped me out!
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)
Thank you both for feedback! This post is the most googled on the blog, and I always wonder if it actually helps anybody out. :-)
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.
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!
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;
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;
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
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!
Thanks for the blog! It helped me today :)
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
Thanks very much, as with others, the whitespace/syntax error was hosing me big time.
hubba hubba
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
:D Just what i needed!
Thanks
Post a Comment
Links to this post:
Create a Link
<< Home