Online Hibernate Training just in Rs. 1000

One week online training in Hibernate ORM framework. Learn Hibernate Framework and enhance your skills. Best Online Hibernate training at 90% discount. Join Now!! Offer is valid Only for limited Period!!! Online Training

MySQL Coalesce

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

Ads

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

Ads
Share on Google+Share on Google+

MySQL Coalesce

Posted on: January 17, 2009 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Advertisements

Ads
 

Discuss: MySQL Coalesce   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:1
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

Ads

 

Ads