{"id":5655,"date":"2017-02-09T23:37:10","date_gmt":"2017-02-10T06:37:10","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5655"},"modified":"2023-10-10T01:34:16","modified_gmt":"2023-10-10T08:34:16","slug":"mysql-isnull-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-comparison-functions\/mysql-isnull-function\/","title":{"rendered":"MySQL ISNULL Function"},"content":{"rendered":"\n

Summary<\/strong>: This tutorial introduces you to the MySQL ISNULL function and how to use it to handle NULL values.<\/p>\n\n\n\n

Introduction to MySQL ISNULL function<\/h2>\n\n\n\n

The ISNULL<\/code> function takes one argument and tests whether that argument is NULL<\/code><\/a> or not. The ISNULL<\/code> function returns 1 if the argument is NULL<\/code>, otherwise, it returns 0.<\/p>\n\n\n\n

The following illustrates the syntax of the ISNULL<\/code> function:<\/p>\n\n\n

ISNULL(expr)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Consider the following examples:<\/p>\n\n\n
SELECT<\/span> ISNULL<\/span>(NULL<\/span>); -- 1    <\/span>\nSELECT<\/span> ISNULL<\/span>(1<\/span>);  -- 0<\/span>\nSELECT<\/span> ISNULL<\/span>(1<\/span> + NULL<\/span>); -- 1;<\/span>\nSELECT<\/span> ISNULL<\/span>(1<\/span> \/ 0<\/span> ); -- 1;<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL ISNULL function & IS NULL operator<\/h2>\n\n\n\n
The ISNULL<\/code> function shares some behaviors with the IS NULL<\/code> operator. For example, if you have a DATE<\/code> column declared as NOT NULL<\/code><\/a>, you can find the special date '0000-00-00'<\/code> by using the following statement:<\/p>\n\n\n
SELECT<\/span> \n    *\nFROM<\/span>\n    table_name\nWHERE<\/span>\n    ISNULL<\/span>(date<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that MySQL purposely implemented this feature to support ODBC applications because ODBC does not support special date value '0000-00-00'<\/code>.<\/p>\n\n\n\n
Let’s take a look at an example.<\/p>\n\n\n\n
CREATE<\/span> TABLE<\/span> special_isnull (\n    start_date DATE<\/span> NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> special_isnull(start_date) \nVALUES<\/span>('2000-01-01'<\/span>),\n      ('0000-00-00'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    *\nFROM<\/span>\n    special_isnull\nWHERE<\/span>\n    ISNULL<\/span>(start_date); <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The query returned one row while you may expected it will return an empty result set.<\/p>\n\n\n\n
When you want to negate the IS NULL<\/code> operator, you use the NOT<\/code> operator i.e., IS NOT NULL<\/code>. However, for the ISNULL<\/code> function, you use !ISNULL<\/code>.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the MySQL ISNULL function and its special behavior to handle NULL<\/code> values.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Third, query<\/a> data from the special_isnull<\/code> table using the ISNULL<\/code>function:<\/p>\n\n\n
Second, insert <\/a>some data into the special_isnull<\/code> table:<\/p>\n\n\n
First, create a new table<\/a> named special_isnull<\/code> as follows:<\/p>\n\n\n
Notice that if you are trying to find the MySQL alternative to Microsoft SQL Server’s\u00a0ISNULL<\/code>\u00a0function, you should use MySQL’s\u00a0IFNULL<\/code><\/a>\u00a0function instead. Because the\u00a0ISNULL<\/code>\u00a0function is MySQL is different from the Microsoft SQL Server’s\u00a0ISNULL<\/code>\u00a0function.<\/p>\n\n\n\n