`COALESCE()`

You can use the coalesce()function as shorthand for case statements. The coalesce() function replaces null values in your dataset with another value. The function takes a series of arguments and returns the first value that is not null.

In this query, the first case statement says that if the City value is not null, return the City value. Otherwise, return NULL. For the second case, it says if a Country value is not null, return the Country. If it is null, return the string “Unknown.”

1SELECT CASE WHEN City
2IS NOT NULL
3THEN City
4ELSE NULL
5END AS City,
6CASE WHEN Country
7IS NOT NULL
8ELSE 'Unknown'
9END As Country
10FROM "Superstore"
11Group by City, Country;

Here’s the same query rewritten with coalesce().

1SELECT COALESCE(City, NULL) as City, COALESCE(Country, 'Unknown') as Country
2FROM "Superstore"
3GROUP BY City, Country;

There are no null City or Country values in the first five results returned from the query.

City	Country
Aberdeen	United States
Abilene	United States
Akron	United States
Albuquerque	United States
Alexandria	United States