mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN; -> 1, 1, 1 

• IS NOT boolean_value 

Tests a value against a boolean value, where boolean_value  can be TRUE , FALSE , or UNKNOWN . 

mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN; -> 1, 1, 0 

• IS NULL 

Tests whether a value is NULL . 

mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL; -> 0, 0, 1 

To work well with ODBC programs, MySQL supports the following extra features when using IS NULL : 

•If sql_auto_is_null  variable is set to 1, then after a statement that successfully inserts an automatically generated AUTO_INCREMENT  value, you can find that value by issuing a statement of the following form: 

SELECT * FROM tbl_name  WHERE auto_col  IS NULL 

If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID()  function. For details, including the return value after a multiple-row insert, see Section 12.16, “Information Functions” . If no AUTO_INCREMENT  value was successfully inserted, the SELECT  statement returns no row. 

The behavior of retrieving an AUTO_INCREMENT  value by using an IS NULL  comparison can be disabled by setting sql_auto_is_null = 0 . See Section 5.1.8, “Server System Variables” . 

The default value of sql_auto_is_null  is 0. 

•For DATE  and DATETIME  columns that are declared as NOT NULL , you can find the special date ’0000-00-00’  by using a statement like this: 

SELECT * FROM tbl_name  WHERE date_column  IS NULL 

This is needed to get some ODBC applications to work because ODBC does not support a ’0000-00-00’  date value. 

See Obtaining Auto-Increment Values, and the description for the FLAG_AUTO_IS_NULL  option at Connector/ODBC Connection Parameters. 

• IS NOT NULL 

Tests whether a value is not NULL . 

mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL; -> 1, 1, 0 

• ISNULL( expr ) 

If expr  is NULL , ISNULL()  returns 1 , otherwise it returns 0 . 

mysql> SELECT ISNULL(1+1); -> 0 mysql> SELECT ISNULL(1/0); 

