COALESCE  function in Oracle SQL

Topic Introduction: In Oracle SQL, the COALESCE function is used to return the first non-NULL expression in a list of expressions. It's particularly useful when you want to find the first non-NULL value among multiple columns or expressions and use it in your query. The COALESCE function takes multiple arguments and returns the value of the first non-NULL argument. If all arguments are NULL, it returns NULL.

The syntax for the COALESCE function is as follows:

COALESCE(expression1, expression2, ..., expressionN)

expression1, expression2, ..., expressionN: The list of expressions to evaluate. You can provide multiple expressions separated by commas.

Here's an example of how to use the COALESCE function in Oracle SQL:

SELECT employee_id,

       COALESCE(nickname, first_name, last_name) AS display_name

FROM employees;

In this example:

The COALESCE function is used to find the first non-NULL value among the nickname, first_name, and last_name columns.

If the nickname is not NULL, it will be used as the display_name.

If the nickname is NULL but the first name is not NULL, the first name will be used.

If both nickname and first_name are NULL but last_name is not NULL, last_name will be used.

If all three columns are NULL for an employee, the result for display_name will be NULL.

In this way, you can create more informative and flexible queries that handle NULL values and prioritize non-NULL values among multiple columns or expressions.