Definition of a NULL

---

NULL is the term used in the SQL standard and database management systems to describe a value that is not known. It is important to note that while a NULL column value is an unknown value, it does still have a type. There is no such thing as an unknown column value that also has an unknown type. The only time a NULL value can also have an unknown type is in the case of a NULL constant:

NULL

NULL Assignments

---

It is important to remember that a NULL is not the same as a zero value with numeric columns such as INTEGER columns, and that a NULL is not the same as an empty string value with string columns such as VARCHAR columns. Assigning any non-NULL value to a column will result in the column value being known and not NULL. Likewise, assigning a NULL to a column is the only way to set a column's value to NULL. For example, the following UPDATE statement will result in a State column value that is empty, but still not NULL:

UPDATE Customer SET State=''

In order to set the State column to NULL, you would need to use this UPDATE statement:

UPDATE Customer SET State=NULL

NULLs and Operators

---

The primary rule to remember with NULLs is that any operator that uses a NULL as an operand will result in a NULL. In other words, it is impossible for any operator using an unknown value to return a known value. For example, in the following UPDATE statement any rows with a NULL in the Quantity column will still have a NULL in the Quantity column after the statement is executed:

UPDATE Orders SET Quantity=(Quantity + 10)

This is also the case with aggregate functions like MIN, MAX, SUM, COUNT, etc. that operate on an individual column. Any row values having a NULL in the column being operated on will be ignored for the purposes of the operation. In addition, any aggregate functions that operate on an individual column will also return a NULL value as the result of the operation if no rows are visited while executing the aggregate function. This can be the case when the JOIN or WHERE clauses filter the available rows so that the aggregate operation is not executed.

FALSE AND NULL results in FALSE
TRUE OR NULL results in TRUE

These results occur because in each case the NULL, or unknown value, is irrelevant to the outcome of the operation. ElevateDB already knows enough to be able to give an accurate answer, and it simply wouldn't matter if the NULL was actually a known value. In each case the result would be the same even with a known value instead of the NULL.

Preventing NULLs in Columns

---

In order to prevent NULLs from being allowed in a given column, you may use the NOT NULL check constraint on a column when creating it via a CREATE TABLE or ALTER TABLE statement. This will prevent any row from being added or updated with a NULL specified for the column.

NULLs and Primary and Unique Key Constraints

---

Primary key constraints require that all of the columns that make up the constraint contain a non-NULL value, irregardless of any NOT NULL check constraints defined for the column(s).

If all of the columns that make up a unique key constraint contain NULLs, then the unique key constraint is not enforced. In other words, unique key constraints allow multiple rows with NULLs in the unique key columns. Only known values are used to enforce the unique key constraint.