Use of COALESCE in SQL Server:
Let us understand this by taking an example
create table candidate
( id int identity ,primaryemail varchar(20),secondaryemail varchar(20))
insert into candidate values ('avi@gmail.com',null)
insert into candidate values ('siva@gmail.com',null)
insert into candidate values ('ravii@gmail.com',null)
insert into candidate values ('bavi@gmail.com',null)
insert into candidate values (null,'chiull@gmail.com')
insert into candidate values (null,'boht@gmai.com')
insert into candidate values ('pandu@gmail.com','kaya@gmail.com')
QUERY:
select coalesce(primaryemail,secondaryemail) from candidate
Here in this example, the Candidate table is shown to include three columns with information about a Candidate:
1. id
2. PrimaryEmail
3. SecondaryEmail
COALESCE in the SELECT statement , selects the PrimaryEmail if it is not null. If the PrimaryEmail is null then SecondaryEmail will be selected. If both PrimaryEmail and SecondaryEmail is present then only PrimaryEmail is selected. So, COALESCE returns the first nonnull column among the list of columns passed. If both PrimaryEmail and SecondaryEmail is NULL, COALESCE returns NULL.
Differenece between isnull and coalesce ?
Coalesce is use for more than two columns where as Isnull works only for two column. Also you can use coalesce function in joining to get first not null value from more than two different table.
http://venkatsqlinterview.blogspot.in/
No comments:
Post a Comment