We have seen the SQL SELECT command along with the WHERE caluse to fetch data from a MySQL table, but when we try to give a condition, which compares the field or column value to NULL, it does not work properly.
To handle this type of situation, MySQL provides three operators-
- IS NULL – This operator returns true, if the column value is NULL.
- IS NOT NULL – This operator returns true, if the column value is not NULL.
- <=> – This operator compares values, which (unlike the = operator) is true even for two NULL values.
The conditions involving NULL are special. You can’t use = NULL or !=NULL to look for NULL values in columns. Such comparisons always fail because it is impossible to tell wheteher they are true or not. Some- times, even NULL=NULL fails.
To look for columns that are or are not NULL, use IS NULL or IS NOT NULL.
Handling NULL values
Select * from tblstudents where Class IS NULL;
Select * from tblstudents where Class IS NOT NULL;
You can see that = and != do not work with the NULL Values.
Select * from tblstudents where Class=NULL; Select * from tblstudents where Class!=NULL;