Coalesce is a simple but useful SQL function I use quite often. It returns the first non-null value in a list of values.
A common real world application for this function is when you are trying to join data from multiple sources. If you work for a company known for acquiring other companies, your HR tables are bound to be full of all kinds of inconsistencies.
In this example below, you will see a snip-it of an HR table created by merging tables from two companies. The first two employees have an emp_id and the last one has a b_emp_id. The last one, Priyanka, is an employee from a newly acquired company, so her emp_id is from the HR table of the newly acquired company.
So if you just want to merge the 2 emp_id columns into one, you can use the coalesce() function.
The syntax for coalesce is: coalesce (col1,col2,….) as alias
Coalesce takes the first non-null. In the example below, we check emp_id first, if it has a value, we use that. If emp_id is null, we look for the value in b_emp_id.
select emp_nm, coalesce (emp_id, b_emp_id) as emp_id from employee_id
If you want to try this for yourself, here is the code to build the table out for you.
CREATE TABLE employee_id (
emp_nm nvarchar(30) not null,
emp_id nvarchar(8),
b_emp_id nvarchar(8)
PRIMARY KEY(emp_nm) );
INSERT INTO employee_id
(emp_nm, emp_id)
VALUES
('Bob', 'A1234567'),
('Lisa', 'A1234568')
INSERT INTO employee_id
(emp_nm, b_emp_id)
VALUES
('Priyanka', 'B1234567');
