NULL

NULL indicates that a value does not exist

 

It has NO value and is NOT comparable or equivalent to the number zero

 

Can be used as a place holder for unknown values

 

Consider the table:

mysql> select * from Students;
+----+-------+---------+------+---------+------+----------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study    | Score |
+----+-------+---------+------+---------+------+----------+-------+
|  1 | Pam   | Frost   | NULL | London  | 1065 | MySQL    |    98 |
|  2 | James | Cross   | 20   | Watford | NULL | Music    |    88 |
|  3 | Andy  | Jones   | 34   | Croydon | 8198 | Markets  |    64 |
|  4 | David | Smith   | 42   | London  | NULL | Antiques |    82 |
|  5 | Julia | James   | NULL | London  | 2108 | Maths    |    77 |
|  6 | Phil  | Watson  | 36   | Hook    | 9437 | Cars     |    44 |
|  7 | Terry | Waite   | 58   | Manc    | 2643 | Music    |    59 |
|  8 | Alex  | Rose    | 49   | Cheam   | NULL | C        |    70 |
+----+-------+---------+------+---------+------+----------+-------+
8 rows in set (0.00 sec)

 

Use IS NULL to obtain the records where a specified field is NULL:

mysql> select * from Students where PIN IS NULL;
+----+-------+---------+------+---------+------+----------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study    | Score |
+----+-------+---------+------+---------+------+----------+-------+
|  2 | James | Cross   | 20   | Watford | NULL | Music    |    88 |
|  4 | David | Smith   | 42   | London  | NULL | Antiques |    82 |
|  8 | Alex  | Rose    | 49   | Cheam   | NULL | C        |    70 |
+----+-------+---------+------+---------+------+----------+-------+
3 rows in set (0.00 sec)

 

Conversely IS NOT NULL can be used to obtain records where the specified field is not NULL:

mysql> select * from Students where PIN IS NOT NULL ;
+----+-------+---------+------+---------+------+----------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study    | Score |
+----+-------+---------+------+---------+------+----------+-------+
|  1 | Pam   | Frost   | NULL | London  | 1065 | MySQL    |    98 |
|  3 | Andy  | Jones   | 34   | Croydon | 8198 | Markets  |    64 |
|  5 | Julia | James   | NULL | London  | 2108 | Maths    |    77 |
|  6 | Phil  | Watson  | 36   | Hook    | 9437 | Cars     |    44 |
|  7 | Terry | Waite   | 58   | Manc    | 2643 | Music    |    59 |
+----+-------+---------+------+---------+------+----------+-------+
5 rows in set (0.01 sec)

 

 

 

*Note: CANNOT use the equality operator, as NULL = NULL will fail

Leave a Reply