Search code examples
mysqlsqlbooleanboolean-expression

How is NOT(IS NULL) different from IS NOT NULL in SQL?

What's wrong with this SQL query?

SELECT * FROM database.tablename  
WHERE columnname NOT (IS NULL)

since IS NULL is a Boolean value, why doesn't NOT negate it? rather it returns this
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(IS NULL)' at line 2

Solution

  • It's purely syntax error.

    There is no syntax like below

    SELECT * FROM database.tablename  
WHERE columnname (IS NULL)

    columnname became function in above example. Right example will be

    SELECT * FROM database.tablename  
WHERE columnname IS NULL

    So if you want to use Not before IS Null you need use it like below:

    SELECT * FROM database.tablename  
WHERE Not (columnname IS NULL)

    Since (columnname IS NULL) is the express not (IS NULL)

    But the conventional way is to use like below:

    SELECT * FROM database.tablename  
WHERE columnname IS NOT NULL

    (Above two statements will return the same result.)