using coalesce in sql | coalesce Example

hi in this post i will show what coalesce() function do in sql server using an example.

Example :

CREATE TABLE empDetails (
 nameid INT identity PRIMARY KEY,
 NAME VARCHAR(1000),
 addres VARCHAR(1000),
 mobile VARCHAR(1000)
 )

INSERT INTO empDetails (NAME,addres,mobile)
VALUES ('Ronnie','india',NULL)

INSERT INTO empDetails (NAME,addres,mobile)
VALUES (NULL,'USA','123456789')

INSERT INTO empDetails (NAME,addres,mobile)
VALUES ('Atul',NULL,'123456789')

INSERT INTO empDetails (NAME,addres,mobile)
VALUES ('Abilash',NULL,'123456789')

INSERT INTO empDetails (NAME,addres,mobile)
VALUES (NULL,'india',NULL)

INSERT INTO empDetails (NAME,addres,mobile)
VALUES (NULL,NULL,'123456789')

INSERT INTO empDetails (NAME,addres,mobile)
VALUES (NULL,NULL,NULL)  

select * from empDetails
select nameid , coalesce(name,addres,mobile) as EmpDetails from empDetails 
select nameid , coalesce(name,addres,'') as EmpDetails from empDetails

1. So coalesce returns the first non null value from the all columns mentioned in the  coalesce() function.

2. suppose the first row is having 3 columns of which first two columns having   null value and 3rd column having valid value, then coalesce() will return the 3rd-column value.

3. if all the columns of a row are null then it will return null.