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');