Coalesce in SQL

COALESCE function returns the first non-NULL value for each row in a set of columns.

Dataset:

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.