MySQL Tips

We have seen SQL SELECT command along with WHERE clause to fetch data from MySQL table. But when we try to give a condition which compare field or column value to NULL it does not work properly.

To handle such situation MySQL provides three operators

IS NULL: operator returns true of column value is NULL.

IS NOT NULL: operator returns true of column value is not NULL.

<=> operator compare values, which (unlike the = operator) is true even for two NULL values

tbl

....................................

id name

................................

1 NULL

2 ABC

3 XYZ

4 NULL

////////////////////////////////////////////Wrong

mysql> SELECT * FROM tbl WHERE name= NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM tbl WHERE name != NULL;
Empty set (0.01 sec)

///////////////////////////Correct

mysql> SELECT * FROM tbl WHERE name IS NULL;
Output :
Id Name
1  NULL
4  NULL

mysql> SELECT * from tbl WHERE name IS NOT NULL;
Output :
Id Name
2  ABC
3  XYZ

///////////////////////////////////
What is the use of i-am-a-dummy-flag in mysql?
It makes mysql engine to refuse UPDATE,DELETE command where WHERE clause is not present.