HOME RESOURCES PLACEMENTS CONTACT FEEDBACK SUBSCRIBE
QUICK LINKS .NET Articles
.NET Training Tutorial
HR Interview Questions
.NET Interview Questions
SQL Interview Questions
JAVA Interview Questions
Back to the list of all SQL Server 2000 / 2005 Interview Questions

What is COALESCE / Why do we use COALESCE?
Let us try to understand the use of COALESCE with an example.

 In this example, the Candidate table is shown to include three columns with information about a Candidate:
1. Candidate_id
2. PrimaryEmail
3. SecondaryEmail

COALESCE in the above 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.

SET NOCOUNT ON

GO

USE master

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

      WHERE TABLE_NAME = 'Candidate')

   DROP TABLE Candidate

GO

CREATE TABLE Candidate

(

   Candidate_id      tinyint    identity,

   PrimaryEmail   nvarchar(50)   NULL,

   SecondaryEmail   nvarchar(50)   NULL,

)

GO

INSERT Candidate VALUES('abc@gmail.com',NULL)

INSERT Candidate VALUES('xyz@gmail.com', NULL)

INSERT Candidate VALUES('def@gmail.com', NULL)

INSERT Candidate VALUES(NULL,'123@gmail.com')

INSERT Candidate VALUES(NULL, '456@gmail.com')

INSERT Candidate VALUES(NULL, '789@gmail.com')

INSERT Candidate VALUES('xox@gmail.com', '789@gmail.com')

GO

SET NOCOUNT OFF

GO

SELECT COALESCE(PrimaryEmail,SecondaryEmail) AS Email

FROM Candidate

GO
COALESCE can also be used in joins as shown in the example below. If the Candidate table has a non value in the Email column, then the value is selected. If the Email column is null in the Candidate Table then, CompanyEmail from CandidateCompany Table is selected.

SET NOCOUNT ON

GO

USE master

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

      WHERE TABLE_NAME = 'Candidate')

   DROP TABLE Candidate

GO

CREATE TABLE Candidate

(

   CandidateId tinyint identity primary key,

   FullName       nvarchar(50)   NULL,

   Email          nvarchar(50)   NULL

)

GO

INSERT Candidate VALUES('abc','abc@gmail.com')

INSERT Candidate VALUES('xyz', NULL)

INSERT Candidate VALUES('123', NULL)

GO

 

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

      WHERE TABLE_NAME = 'CandidateCompany')

   DROP TABLE CandidateCompany

GO

CREATE TABLE CandidateCompany

(

   CompanyId      tinyint identity,

   CandidateId    tinyint REFERENCES Candidate(CandidateId),

   CompanyEmail   nvarchar(50)   NULL

)

GO

INSERT CandidateCompany VALUES(1,'Company1@gmail.com')

INSERT CandidateCompany VALUES(2,'Company2@gmail.com')

INSERT CandidateCompany VALUES(3,'Company3@gmail.com')

GO

 

SET NOCOUNT OFF

GO

SELECT Cand.FullName,COALESCE(Cand.Email,Comp.CompanyEmail) AS Email

FROM Candidate Cand INNER JOIN  CandidateCompany Comp

ON Cand.CandidateId = Comp.CandidateId

GO

Back to the list of all SQL Server 2000 / 2005 Interview Questions


Your Questions/Comments/Feedback:
If you have some questions that needs an answer you can post them here. If you find this page useful please post your comments and feedback.
Title/Question:
Name:
Email:
Comments: