Scaler
Academy
Data Science
Neovarsity
New
Skill Test
Free Masterclass
Search for Articles, Topics
Coalesce MySQL

Coalesce MySQL

By Manish Jha
4 mins read
Last updated: 4 May 2023
197 views
Learn via video courses
Topics Covered

Overview

The coalesce MySQL function returns the first non-null value from a list of expressions. It takes a list of values as a parameter and then evaluates the list. The coalesce MySQL function replaces the null values provided in the list with the value defined by the user. Then it starts evaluating the expression. This evaluation of the given list is carried out in a certain sequence and always returns the first non-null value.

Syntax of MySQL Coalesce() Function

The following is the syntax of coalesce MySQL function:

Parameters of MySQL Coalesce() Function

The coalesce MySQL function takes one (or more) values as a list of arguments in the following way:

  • These values can be of any data type or can be null values.
  • These values are then treated as a list and the coalesce MySQL function goes through the list in a particular order.

Note: For a value to be null, it should be specified explicitly. Unlike other languages, SQL does not treat empty values as null values.

Return Value of MySQL Coalesce() Function

  • The coalesce MySQL function traverses through the values passed as parameters and returns the first not-null value.
  • The coalesce MySQL function returns NULL when all the values passed as parameters are null.

How does the MySQL Coalesce() Function Work?

As discussed above, the coalesce MySQL function is used to return the first non-null value from a list of expressions. The coalesce MySQL function takes a sequence of values that can either be of the same or different data types.

Note: The coalesce MySQL function is a syntactic short form of case expression in MySQL. It evaluates each argument in the list as a case and returns the first case which is not null.

For example, suppose we have the following list where we want to find the first non-null value:

Now, when the above list is passed to the coalesce() function, it will start evaluating the list from the left. Firstly it will check if the first value is null. Since the first value is null, it will proceed to the next value. Again it will check if the second value is null. Since the second value 3456 is not null, it will break the search for the first non-null value and return 3465.

Examples

Example 1: Using coalesce MySQL function with all numeric parameters

In this example, we will use the coalesce MySQL function to find the first non-null value from a sequence of numeric parameters passed.

Code:

Output:

Explanation of the example:

In the above example, we have passed the list null, null, 140, 99, null, and 1010 to our coalesce MySQL function. This function then traverses left to right through each value passed and checks if it is null. When the coalesce MySQL function encounters 140, the null check will fail, thus it will return 140.

Example 2: Using coalesce MySQL function with all string parameters

In this example, we will use the coalesce MySQL function to find the first non-null value from a sequence of string parameters passed.

Code:

Output:

Explanation of the example:

In the above example, we have passed the list null, 'Peter', null, 'Jon', 'Bruce', null to our coalesce MySQL function. This function then traverses left to right through each value passed and checks if it is null. When the coalesce MySQL function encounters 'Peter', the null check will fail, thus it will return 'Peter'.

Example 3: Using coalesce MySQL function with mixed parameters

In this example, we will use the coalesce MySQL function to find the first non-null value from a sequence of mixed parameters passed.

Code:

Output:

Explanation of the example:

In the above example, we have passed the list null, null, 100, 'Jon', 'Bruce', null, 9090 to our coalesce MySQL function. This function then traverses left to right through each value passed and checks if it is null. When the coalesce MySQL function encounters 100, the null check will fail, thus it will return 100.

Example 4: Using coalesce MySQL function with all null parameters

In this example, we will use the coalesce MySQL function to find the first non-null value from a sequence of all null parameters passed.

Code:

Output:

Explanation of the example:

In the above example, we have passed the list null, null, null to our coalesce MySQL function. This function then traverses left to right through each value passed and checks if it is null. Since it does not have any non-null value, it will return NULL.

Conclusion

  • The coalesce MySQL function is used to return the first non-null value from a list of expressions.
  • It takes one (or more) values as parameters.
  • The values can be of the same or different data types.
  • The coalesce MySQL function returns a null value if no non-null value is passed as an argument.
  • The coalesce MySQL function is equivalent to a case statement in MySQL.

See Also

  • Not Null in SQL

MCQs

  1. What does coalesce MySQL function return?

    • a) First null value.
    • b) First non-null value.
    • c) Last non-null value.
    • d) Last null value.

    Ans: b

  2. What happens when no not-null value is passed to coalesce MySQL function?

    • a) It displays an error.
    • b) It returns 0.
    • c) Null is returned.
    • d) It returns all values passed as parameters.

    Ans: c

  3. Which of the following is false for coalesce MySQL function?

    • a) It is a syntactic short form of case expression in MySQL.
    • b) It takes a list of values as a parameter.
    • c) It can take parameters of different data types.
    • d) It returns the total number of non-null values in a list.

    Ans: d