1.4.12.8 NULL Values

An important feature of web.database is its ability to support NULL values. A field which contains a NULL value is simply a field where no value has been set or the value as been set to contain no value. This is quite different, for example, from a String field which has been set a value '', an empty string.

Original Table (used in the examples)

+----------+-----------+--------+-------------+
| LastName | FirstName | Number | DateOfBirth |
+----------+-----------+--------+-------------+
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Doe'    | 'John'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |
+----------+-----------+--------+-------------+

Our query

UPDATE Person SET FirstName=NULL WHERE LastName='Doe'

Our table now looks like this:

+----------+-----------+--------+-------------+
| LastName | FirstName | Number | DateOfBirth |
+----------+-----------+--------+-------------+
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Doe'    | NULL      | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |
+----------+-----------+--------+-------------+

This is quite different from this query which simply sets the FirstName to the string 'NULL' not the value NULL:

UPDATE Person SET FirstName='NULL' WHERE FirstName IS NULL

Our table now looks like this:

+----------+-----------+--------+-------------+
| LastName | FirstName | Number | DateOfBirth |
+----------+-----------+--------+-------------+
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Doe'    | 'NULL'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |
+----------+-----------+--------+-------------+

This is one of the reasons why it is important to use the correct quotations around values in you SQL.

Note: We use the IS operator rather than the = operator to compare fields to NULL values.

If you inserted a row into the table without specifying all the columns the columns you had not specified would contain the value NULL unless you had specified a DEFAULT value when you created the table.