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.