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 ballerina @ 10:31 am
27 Comments:
thanks for the example, helped a newbie out :)
Great to hear, that's what I was hoping. :-)
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
really helpful. thanks.
Great this is also available in MySQL.
Very helpfull
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'
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
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')
Just the SQL example I was needing. The manual didn't really show Coalesce in the context of a full query. Perfect. Thanks!
THANKS
Thanx Great tuto
thanx,its a great tutorial
thanks laila
Thanks for example
Post a Comment
<< Home