Description of IFNULL () and COALESCE () functions in mysql, ifnullcoalesce

Description of IFNULL () and COALESCE () functions in mysql, ifnullcoalesce

In MySQLisnull()The function cannot be used as an alternative null value!

As follows:

First, there is a table named "business:

SELECT ISNULL(business_name,'no business_name') AS bus_isnull FROM business WHERE id=2

If you run the command directly, an error is returned:

Error code: 1582

Incorrect parameter count in the call to native function 'isnull'

So,isnull()Functions won't work in mysql. Availableifnull()Andcoalesce(). As follows:

Useifnull()Function:

SELECT IFNULL(business_name,'no business_name') AS bus_ifnull FROM business WHERE id=2

Running result:

When the queried value is not null:

SELECT IFNULL(business_name,'no business_name') AS bus_ifnull FROM business WHERE id=1

The result is as follows:

Usecoalesce()Function:

SELECT COALESCE(business_name,'no business_name') AS bus_coalesce FROM business WHERE id=2

The result is as follows:

 

When the query value is not null:

SELECT COALESCE(business_name,'no business_name') AS bus_coalesce FROM business WHERE id=1

Where:coalesce()You can also return the first non-null value. As follows:

SELECT COALESCE(business_name,district_id,id) AS bus_coalesce FROM business WHERE id=2

So,isnull()How to use it in mysql? The answer is after where. As follows:

SELECT * FROM business WHERE ISNULL(business_name)

The result is as follows:

Similarly,is null Andis not null It is also used after where.

SELECT * FROM business WHERE business_name IS NULL

The result is as follows:

SELECT * FROM business WHERE business_name IS NOT NULL

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.