Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • What is the use of IFNULL Operator in MySQL?

    • 0
    • 1
    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 709
    Comment on it

    In MySQL, the IFNULL operator is used as conditional statement for NULL values. When we need to fetch NULL values with LIKE operator then we can use this operator.

    Example:

    We have a table "employee" as below:

      

      employee
        id first_name salary country
        .......................................................
        1 John        10000  Canada
        2 Chris       20000  NULL
        3 Max         30000  NULL
        4 Jenny       25000  Canada


    Use the IFNULL operator

    The below statement selects all employee with "country" containing pattern "can" in the "employee" table and NULL values as well:

       

        SELECT * FROM employee
        WHERE IFNULL(country, '') like '' or country LIKE '%can%';

    Result

       

        id first_name salary country
        .......................................................
        1 John        10000  Canada
        2 Chris       20000  NULL
        3 Max         30000  NULL
        4 Jenny       25000  Canada

    To get the records that do not match with the specified pattern we use NOT keyword as below, this statement selects all emplyess not having country containing pattern "can" and NULL values as well:

       

        SELECT * FROM employee
        WHERE IFNULL(country, '') like '' or country NOT LIKE '%can%';

    Result

       

        id first_name salary country
        .......................................................
        2 Chris       20000  NULL
        3 Max         30000  NULL

    Hope this will help you :)

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: