coalesce function in SQL server with example

Coalesce Function in SQL Server

Coalesce function will use in very rare case in real time, but it is very important interview question in sql server. Both Isnull function and Coalesce function works similar, so there are chances to get confuse. Let we discuss about Coalesce function with example so that you can remember easily.

What is Coalesce function in sql server?

Coalesce function is used to "returns the first nonnull expression among its arguments"



what is syntax of Coalesce function in sql server?

COALESCE( expression1, expression2, ... expression_n )

Coalesce function in sql server with example

Run the below query in sql server management studio (ssms)

SELECT COALESCE(NULL, NULL, 'FreshersJunction', NULL, 'SQL Server')

The Output is: FreshersJunction

By the above example we can clearly understand that Coalesce function returns the first nonnull expression among its arguments in sql server.

Now we will try some more examples for coalesce function in SQL Server

SELECT COALESCE(NULL, NULL, NULL, NULL, 'SQL Server')

The output is : SQL Server

SELECT COALESCE('Function', NULL, NULL, NULL,NULL)

The output is : Function

clearly observer the functionality of coalesce function, its looking like case statement, right?

Yes, coalesce function is shortcut for the CASE statement (only in the case of checking not null value). See the below example

CASE

WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END

I hope now you have clear understanding on coalesce function.

Now, in what scenarios we will use coalesce function in sql sever with example.


Create the table by using below query

Create table Employee_Contact_Number
(Eid int, Ename nvarchar(255), Mobile_Number bigint,
Home_Number bigint, Office_Number bigint)

Insert the values by using below query in sql server

insert into Employee_Contact_Number values (101, 'Mahesh', 9999999999, Null, null)
insert into Employee_Contact_Number values (102, 'Pawan', Null, 0801234, null)
insert into Employee_Contact_Number values (103, 'Venkat', null, Null, 00912349686)
insert into Employee_Contact_Number values (104, 'Vasu', 123467890, Null, 0079911129)
insert into Employee_Contact_Number values (105, 'Khadar', 90767433893, 56972736333, 040782974645)

Now check the table

select * from Employee_Contact_Number

Output:

Eid	Ename	Mobile_Number	Home_Number	Office_Number
101	Mahesh	9999999999	NULL	NULL
102	Pawan	NULL	801234	NULL
103	Venkat	NULL	NULL	912349686
104	Vasu	123467890	NULL	79911129
105	Khadar	90767433893	56972736333	40782974645

Now from the above table we should get at least one contact number for each employee and 1st preference is Mobile_Number and 2nd preference is Home_Number and 3rd preference is Office_Number.

In the above scenario coalesce function will work exactly

select Eid, Ename, coalesce(Mobile_Number,Home_Number,Office_Number) as Contact_Number from Employee_Contact_Number

The output for the above query is

Eid	Ename	Contact_Number
101	Mahesh	9999999999
102	Pawan	801234
103	Venkat	912349686
104	Vasu	123467890
105	Khadar	90767433893

Now you understood scenarios of coalesce function in sql server, right?

To get regular job updates and technical sql server interview questions, share your email id with us.

Enter Your Mail ID Here and confirm

If you have any queries related to SQL Server, please comment here. We will give you update as soon as possible.

You May also search in Google about coalesce function as:
coalesce function in sql server with example

What does coalesce do in SQL?

coalesce sql server example

coalesce function in sql server 2012

coalesce function in sql server 2008 with example