MySQL ISNULL() Function

In SQL language, ISNULL() function can be useful for processing results that have null data. However, this function is used differently depending on the database management system. In MySQL ISNULL() function takes a single parameter and allows to check if a data is null.
 

Syntax:

The SQL function can be used in SQL queries easily. The following queries are typical examples of the use of this function.

SELECT ISNULL(NULL); -- output 1
SELECT ISNULL(2*4); -- output 0
SELECT ISNULL(2/0); -- output 1

 

 

Example :

Suppose we have the following MySQL table.

+-----------+-----------+-----------+------------+
|  user_id  | user_name | user_city | country_id |
+-----------+-----------+-----------+------------+
|    1      |   Alex    | New York  |  1         |
|    2      |   Emily   | Chicago   |  NULL      |
|    3      |   Jean    | Mumbai    |  NULL      |
|    4      |   Bob     | Sydney    |  1555      |
|    5      |   Xavier  | London    |  4         |
+-----------+-----------+-----------+------------+

Let’s say we want to display only the results where the country_id is null, it would be possible to use the IS NULL clause but it is also possible to use ISNULL() function and check if the result is equal to 1.

SELECT *
FROM users
WHERE ISNULL(country_id) = 1

The result of this SQL query will be the following table:
 
Output:

+-----------+-----------+-----------+------------+
|  user_id  | user_name | user_city | country_id |
+-----------+-----------+-----------+------------+
|    2      |   Emily   | Chicago   |  NULL      |
|    3      |   Jean    | Mumbai    |  NULL      |
+-----------+-----------+-----------+------------+

In this result, you can clearly see that only the rows where the country_id is NULL are returned.
 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More