• Ekta Aggarwal

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)


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.