Coalesce in SQL
The COALESCE function returns the first non-null value from a list of expressions. It can be used in SQL data analysis to handle cases where a column may contain null values.
Here’s an example:
Suppose you have the following users table.
You could use the COALESCE function to return the first non-null value from a list of expressions.
For example, you could select each user’s location and use the COALESCE to return the first non-null value from these columns, then name the resulting column “location”.
SELECT first_name, COALESCE(city, state, country) AS location FROM users;
If the “city” column is null, then the COALESCE will evaluate the “state” column, and if that is null as well, it will assess the “country” column. If all three columns are null for a particular row, then the result for that row will be null.
The result looks like this: