Skip to content
Advertisement

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

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement