Detailed explanation of IFNULL() and COALESCE() functions to replace null in MySQL

The isnull() function cannot be used as a substitute for null values ​​in MySQL!

as follows:

First, there is a table named business:

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

Running it directly will give an error:

Error code: 1582

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

Therefore, the isnull() function does not work in MySQL. You can use ifnull() and coalesce() instead. as follows:

Use ifnull() function:

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

Running results:

When the queried value is not null:

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

The results are as follows:

Use coalesce() function:

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

The results are as follows:

When the query value is not null:

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

Among them: coalesce() 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, how to use isnull() in MySQL? The answer is used after where. as follows:

SELECT * FROM business WHERE ISNULL(business_name)

The results are as follows:

Similarly, is null and is not null are also used after where.

SELECT * FROM business WHERE business_name IS NULL

The results are as follows:

SELECT * FROM business WHERE business_name IS NOT NULL 

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.