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.
