Coalesce in SQL
COALESCE function returns the first non-NULL value for each row in a set of columns.
For this tutorial we shall make use of hosp_data:
CREATE TABLE hosp_data( patient int, cardiology date, radiology date , neurology date) INSERT INTO hosp_data(patient,cardiology,radiology,neurology) VALUES (1001,'2020-08-16',NULL,NULL), (1002,NULL,'2017-06-26','2018-02-14'), (1003,NULL,NULL,'2013-03-12'), (1004,'2018-11-27','2020-03-21',NULL), (1005,'2020-04-10','2020-12-09','2020-09-13');
Out data looks as follows:
SELECT * FROM hosp_data;
We want to get the first available non-NULL values among 3 columns cardiology, radiology and neurology.
Note: Sequence of columns matter the most in coalesce, if you change the order of these 3 columns, output of coalesce will be changed accordingly.
SELECT *, coalesce(cardiology,radiology,neurology) as coalesce_output FROM hosp_data;
Note that in last row all 3 dates are available but since first argument of COALESCE was cardiology thus the first non-NULL occurrence would be considered from Cardiology column.