EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL COALESCE
 

SQL COALESCE

Updated March 8, 2023

SQL COALESCE

 

 

Introduction to SQL COALESCE

COALESCE is a predefined built-in Structured Query Language(SQL) function that is used to handle NULL values in the data records. It evaluates a set or list of input parameters in a sequential manner and returns the first non NULL values among them. The COALESCE function terminates once it encounters the first non NULL value and returns it. If all the arguments in the input list are NULL values, then the function will return NULL.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

It is a generic function that is supported in all databases like MYSQL, SQL Server(starting 2008), Azure SQL database, PostgreSQL, Oracle, etc.

Some uses of SQL COALESCE function are :

  • Handling NULL values
  • Running two or more queries as a single query
  • Shortcut alternative to lengthy and time-consuming CASE statements

Syntax and Parameters

The basic syntax for using COALESCE function in SQL is as follows:

SELECT COALESCE(value_1, value_2,value_3,value_4, …value_n);

The parameters mentioned in the above syntax are :

COALESCE() : SQL function that returns the first non-null value from the input list.

value_1, value_2,value_3,value_4, …value_n : The input values that have to be evaluated.

Since COALESCE function is an expression in itself, it can be used in any statement or clause that accepts expressions like SELECT, WHERE and HAVING.

COALESCE function can be considered a specialized version of the CASE statement in SQL.

The syntax of the CASE statement showcasing the function of COALESCE function is as follows :

SELECT column_name1,column_name2, column_name3,
CASE
WHEN column_name1 IS NOT NULL THEN column_name1
WHEN column_name2 IS NOT NULL THEN column_name2 ELSE NULL
END as 'case_name'
FROM table_name;

The parameters mentioned in the above syntax are :

Column_name1, column_name2: The values of columns that have to have coalesced, i.e. if the results of any of these columns result in NULL values, then the first non-NULL value among them will be returned. But if both of them evaluates to NULL, then CASE will return NULL.

Going ahead we will be discussing the above mentioned COALESCE function in great detail.

In order to understand the concept better, we will take the help of the employee’s table( this contains personal details of all the employees).

The data in the “employees” table is as follows :

SQL COALESCE output 1

Examples of SQL COALESCE

Here are a few examples to understand SQL COALESCE better.

Example #1

SQL query to illustrate the use of simple COALESCE function.

Code:

SELECT COALESCE(NULL, NULL, NULL, 'EduCBA', NULL, NULL);

Output:

SQL COALESCE output 2

In this example, we can see that the COALESCE function selects the first not NULL value, it encounters and then terminates.

Example #2

SQL query to find the first non-null values from an employee’s first name and last name.

Code:

SELECT employeeid,firstname, lastname,
COALESCE(firstname,lastname) as 'first not null name'
FROM employees;

Output:

SQL COALESCE output 3

In this example, the first non NULL is returned.

Example #3

SQL query to illustrate COALESCE function as a special case of CASE.

Code:

SELECT employeeid,firstname, lastname,
CASE
WHEN firstname IS NOT NULL THEN firstname
WHEN lastname IS NOT NULL THEN lastname ELSE NULL
END as 'first not null value'
FROM employees;

Output:

SQL COALESCE output 4

Compare the results of this query and that of the query in the third example. We can see that both of them produce the same result. This is because the COALESCE function can be considered as a special case of CASE.

Example #4

SQL query to illustrate the use of COALESCE function on values with different data types.

Code:

SELECT employeeid,firstname, lastname,
COALESCE(employeeid,firstname,lastname) as 'first not null name'
FROM employees;

Output:

output 5

In the above example, we can see that employee id could not be clubbed with first name and last name data values as they have different data types.

In order to solve this problem, we may change the data type of employeeid using the CONVERT function as shown below.

Code:

SELECT employeeid,firstname, lastname,
COALESCE(CONVERT(varchar(50),employeeid),firstname,lastname) as 'first not null values'
FROM employees;

Output:

output 6

Example #5

A complex SQL query to illustrate the use of COALESCE function.

Suppose we want to know the salaries of all the employees in the firm. But in the employee’s table, we can see that the salaries of all employees have not been mentioned. However, hourly rates and commission of employees with missing salary fields have been mentioned. We can use that to compare employee salaries in the following manner.

Code:

SELECT COALESCE(CONVERT(varchar(50),employeeid),firstname,lastname) as 'Employee Identifier',
COALESCE(salary,hourly_rate*8*30 + COALESCE(commission,0))as 'Compensation'
FROM employees
ORDER BY 2 DESC;

Output:

output 7

Using the first COALESCE function, we tried to identify an employee by coalescing employeeid, first name, and last name together and then using the second COALESCE function, we tried to select salary or calculated compensation.

Conclusion

SQL COALESCE function is a built-in function in databases like SQL Server, Azure, etc. that returns the first non NULL value from an input list of arguments. It is very useful in handling null values and serves a quick and easy alternative to SQL CASE statements.

Recommended Articles

We hope that this EDUCBA information on “SQL COALESCE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Subquery
  2. SQL with Clause
  3. SQL Arithmetic Operators
  4. Table in MySQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW