MySQL NULL Values
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.
For Examples
Table tblstudents
| id | Name | Roll_no | Class |
| 1 | Anuj kumar | 10806121 | B.tech |
| 2 | Sanjeev | 10883327 | NULL |
| 3 | Amit | 13123121 | BA |
| 4 | Rahul | 23424323 | NULL |
Handling NULL values
Ex:
|
1 |
Select * from tblstudents where Class IS NULL; |
OUTPUT
| id | Name | Roll_no | Class |
| 2 | Sanjeev | 10883327 | NULL |
| 4 | Rahul | 23424323 | NULL |
Ex:
|
1 |
Select * from tblstudents where Class IS NOT NULL; |
OUTPUT
| id | Name | Roll_no | Class |
| 1 | Anuj kumar | 10806121 | B.tech |
| 3 | Amit | 13123121 | BA |
NOTE :
You can see that = and != do not work with the NULL Values.
|
1 2 |
Select * from tblstudents where Class=NULL; Select * from tblstudents where Class!=NULL; |
