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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.