Home Sql Mysql-example MySQL Coalesce
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

MySQL Coalesce

Advertisement
In this section, you will learn about MySQL Coalesce. It selects the first non-null value of its arguments.

MySQL Coalesce

     

In this section, you will learn about MySQL Coalesce. It selects the first non-null value of its arguments.

Syntax:
coalesce(value,..)
: It returns the first non NULL value in the list. If there are no non-NULL values then it returns NULL.

 

 

 

 

Table: employee

CREATE TABLE `employee` (
`emp_id` int(11) NOT NULL auto_increment,
`emp_name` varchar(100) character set utf8 NOT NULL,   `emp_salary` int(11) NOT NULL,
`emp_startDate` datetime NOT NULL,
`dep_name` varchar(50) NOT NULL, PRIMARY KEY  (`emp_id`)
)

employee table data:

Table: empleavedetails

CREATE TABLE `empleavedetails` ( 
`id` int(11) NOT NULL auto_increment, 
`emp_id` int(11) NOT NULL, 
`no_of_leave` int(11) NOT NULL, 
PRIMARY KEY (`id`) 
)

empleavedetails table data:

Query:

select e.emp_name as Name, l.no_of_leave as NOL from employee e left outer join empleavedetails l on e.emp_id=l.emp_id;

Output: The output contains NULL values in the result list as shown below:

The null values are eliminated from the list using COALESCE. For example:

Query:

select e.emp_name as Name,COALESCE(l.no_of_leave,0) as NOL from employee e left outer join empleavedetails l on e.emp_id=l.emp_id;

Output:

Advertisements

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: January 17, 2009

Ask Questions?    Discuss: MySQL Coalesce   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
Sandesh Daddi
April 4, 2012
Thank you for this tutorial

Thankyou very much, I was unaware about this function in MySQL but I know about the same function in MS SQL Server and your article had given me support for the same... thank you very much Sandesh Daddi www.sanshark.com
DMCA.com