When we apply LIKE keyword to any query then it doesn't return NULL values, and sometimes it required to return NULL values as well. We can do this by using IS NULL or IFNULL 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 IS NULL 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 country is null 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 emplyee not having country containing pattern "can" and NULL values as well:

    SELECT * FROM employee
    WHERE country is null or country NOT LIKE '%can%';

Result

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

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 :)