COALESCE and NULLIF functions
Oracle provides two more functions that carry out a functionality that is similar to the CASE expression in certain scenarios. We can use these in conjunction with or as a variety of the CASE expression.
COALESCE returns the first not null value in a given list of values.
E.g.: Returning the first not null value available in four columns present in a table.
select coalesce(col1, col2, col3, col4)
E.g.: The above example will return the same result as the below statement with the CASE expression.
select case when col1 is not null then col1
when col2 is not null then col2
when col3 is not null then col3
when col4 is not null then col4
The NULLIF function compares two values and does the following.
- Returns null if both values are the same.
- Returns the first value if both values are different.
E.g.: Returning the credits available for customers. The query below will return null if the TOTAL_CREDITS column is equal to the CREDITS_USED column for a customer, else it will return the TOTAL_CREDITS value.
select customer_name, nullif(total_credit, credits_used)
E.g.: The above example will return the same result as the statement below with CASE expression.
select customer_name, case when total_credits = credits_used then null
The maximum number of arguments that can be specified is 255, each WHEN ... THEN pair is counted as two arguments. To avoid this limitation the CASE function can be nested.
This functionality is supported in PL/SQL from Oracle 9i. The CASE expression will make it easy for developers to get more information based on analysis in a single query.