Home » Core Java » sql » SQL Server COALESCE() Function

About Mohammad Meraj Zia

Avatar photo
Senior Java Developer

SQL Server COALESCE() Function

In this article, we will show you about SQL Server COALESCE() Function through detailed examples.

1. Introduction

SQL is a standard language for storing, manipulating, and retrieving data in a database. SQL stands for Structured Query Language. SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987. Although SQL is an ANSI/ISO standard, there are different versions of the SQL language. Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard.

2. SQL COALESCE() function

The COALESCE function returns the first non-NULL value from a series of expressions. The expressions are evaluated in the order in which they are specified, and the result of the function is the first value that is not null. The result of the COALESCE function returns NULL only if all the arguments are null. The expressions can return any data type that can be cast to a common compatible data type.

3. SQL COALESCE() Example

For our examples, we will use SQLite. SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. I am using Mac and SQLite comes to build in. Type SQLite on the terminal to check if it is installed on your machine or not:

~$ sqlite3
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Let us first create a table where we will store the employee data. Run the below command in the SQLite prompt:

CREATE TABLE employee (
    employee_id INTEGER PRIIMARY KEY,
    first_name TEXT NOT NULL,
    surname TEXT NOT NULL,
    email TEXT NOT NULL,
    department TEXT
);

To check if the table got created run .tables command. Now let us insert some data into the employee table:

INSERT INTO employee(employee_id, first_name, surname, email, department)
VALUES (1, 'Mike', 'Hertz', 'mike.hertz@jcg.com', 'Operations');

INSERT INTO employee(employee_id, first_name, surname, email, department)
VALUES (2, 'Ahmed', 'Basheer', 'ahmed.basheer@jcg.com', 'HR');

INSERT INTO employee(employee_id, first_name, surname, email)
VALUES (3, 'Andree', 'Coper', 'andree.coper@jcg.com');

To check if the records are added successfully run the SELECT command:

sqlite> select * from employee;
1|Mike|Hertz|mike.hertz@jcg.com|Operations
2|Ahmed|Basheer|ahmed.basheer@jcg.com|HR
3|Andree|Coper|andree.coper@jcg.com|

Now let us query the details of all employees. For the one who don’t have a department assigned we want to return ‘Department not assigned‘:

sqlite> select first_name,surname, COALESCE(department, 'Department not assigned') AS department from employee;
Mike|Hertz|Operations
Ahmed|Basheer|HR
Andree|Coper|Department not assigned

In the above example, we can see that Andree doesn’t have a department assigned so we return ‘Department not assigned

4. Summary

In this example we looked at COALESCE function. COALESCE function is used to return the first non-null value in a list. If we run SELECT COALESCE(NULL, NULL, 'Java Code Geeks', NULL, 'Example'); , ‘Java Code Geeks’ will be returned. we used SQLite to run our code. You can use any other DBMS if you want.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments