SQL Tutorial – COALESCE() – Replace Null values with another value.

You have rows that contains NULL values and you want to return Non-Null values in place of those null values. For example, In the this table we have a NULL value for George and I want to replace it with a 0.

Solution –

To replace the null values with another values we can use the COALESCE() function in SQL.

The COALESCE() function returns the first Non-Null value in a list.

Syntax –

COALESCE(val1, val2, val3 …, valn)

SELECT COALESCE(NULL, NULL, 5, NULL, 10) as NUM -- return 5

This query returns 5 as this is the first Non-Null value in this list.

Now, to replace all the Null values in our christmas_spending column, we can just pass the column name as the first argument to the COALESCE function and the value with which you want to replace as the second argument.

SELECT
    id,
    name,
    gender,
    COALESCE(christmas_spending, 0) as christmas_spending
FROM
    actors_info

We can see that the Null value for George is replaced with 0.