Where we can use NULL in mysql?

Where we can use NULL in mysql?

Normally, In java, null means nothing. Like that, In mysql, We can use NULL.

While try to insert null in mysql table, then we should not use single quotes while insert null in table. If we use single quote for null, then I will take that as a String.

If we add null, then it automatically added NULL in table.


Example,

create table sample(id integer(20) default NULL, name varchar(60) default NULL);

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | YES  |     | NULL    |       |
| name  | varchar(60) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

insert into sample values(1, null);
1 row in set (0.02 sec)

select * from sample;

+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+

insert into sample values(NULL, 'Uma');
1 row in set (0.02 sec)

select * from sample;

+------+------+
| id   | name |
+------+------+
|    1 | NULL |
| NULL | Uma  |
+------+------+

insert into sample values(3, 'Vidhya');
1 row in set (0.02 sec)

select * from sample;

+------+--------+
| id   | name   |
+------+--------+
|    1 | NULL   |
| NULL | Uma    |
|    3 | Vidhya |
+------+--------+


-> We can check NULL as follows,

select * from sample where name is Not NULL;

+------+--------+
| id   | name   |
+------+--------+
| NULL | Uma    |
|    3 | Vidhya |
+------+--------+


select * from sample where name is NULL;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+



More Info :

http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html

http://stackoverflow.com/questions/3059805/difference-between-mysql-is-not-null-and