NULL in Database Management Systems (DBMS) — a special value (pseudo-value) that can be written into a field of a database (DB) table. NULL corresponds to the notion of an «empty field», that is, «a field containing no value». It was introduced to distinguish, in DB fields, between empty (visually undisplayed) values (for example, a zero-length string) and missing values (when the field has no value recorded in it at all, not even an empty one).
In database theory, the lowercase Greek letter omega (ω) is used to denote the Null value
NULL denotes absence, unknown information. The NULL value is not a value in the full sense of the word: by definition it denotes the absence of a value and can have the type NULL or any other type (CREATE TABLE new_tab AS (SELECT NULL) - the special null type, CREATE TABLE new_table AS (SELECT 10+NULL) - the integer type). Therefore NULL is not equal to the boolean value FALSE, nor to an empty string, nor to zero. Comparing NULL with any value yields the result NULL, not FALSE and not 0. Moreover, NULL is not equal to NULL!
The need for NULL in relational databases
- Opinion 1: NULL is necessary and mandatory for any DB that claims to be relational. In particular, without it, an OUTER JOIN of rows from two tables cannot be correctly built. This was the view held by E. Codd, who explicitly included it as the third of his 12 rules for relational DBMSs. This principle is also enshrined in the latest SQL language standards.
- Opinion 2: The NULL value is not required, and its use is a consequence of a database design error. In a database designed in full compliance with the criteria of normalization, there can be no fields without values, and therefore no special pseudo-value is needed for such fields. In practice, however, for reasons of efficiency, it is often convenient to neglect some of the normalization rules, and one of the costs of such neglect is the appearance of empty fields, for which NULL is precisely intended .
Using NULL in a DB
In DBs that support the concept of NULL, the definition of a table field specifies whether it may be empty. If so, then no value need be written into this field, and the field will hold the value NULL. It is also possible to explicitly write the value NULL into such a field.
As a rule, a DBMS does not allow the value NULL for fields that are part of a table's primary key. In foreign key fields, by contrast, NULL is permitted. The presence of NULL in a foreign key field can be interpreted as an indication that there is no related record, and for such a foreign key the referential integrity rules that are mandatory for any other foreign key value do not need to be enforced.
Operations with NULL
Since NULL is not, in a general sense, a value, using it in arithmetic, string, logical, and other operations is, strictly speaking, incorrect. Nevertheless, most DBMSs support such operations, but introduce special rules for them:
- NULL can be assigned to variables and written into fields, regardless of the declared data type of those variables (fields);
- NULL can be passed into procedures and functions as a legal parameter value. The results of executing such a procedure or function are determined by the operations performed on the parameters within it.
- Any comparison operation with NULL (even the operation «NULL = NULL») yields the result «unknown» (UNKNOWN). The final result then depends on the complete logical expression, in accordance with the truth table of logical operations. If the comparison with NULL is the entire logical operation (and not just part of it), then its result is equivalent to FALSE (an expression of the form IF <something> = NULL THEN <action1> ELSE <action2> END IF will always result in action2 being executed).
- Aggregate and analytic functions (used in SQL as operations over sets and lists) generally ignore NULL values in favor of the valid values of the remaining elements of the set. For example, for the AVG function, intended to find the arithmetic mean of some expression computed for each row in a group, the result is the same as if the rows containing NULL for that expression were not present in the group at all.
- There is a special system function or operation (usually expr IS [NOT] NULL) that returns the boolean value «true» (TRUE) if expr is (is not) NULL, and FALSE otherwise.
In addition, there may be special system functions for conveniently converting NULL to specific values; for example, Oracle has the system function NVL, which returns the value of its parameter if it is not NULL, or a default value if the operand is NULL. The SQL-92 standard defines two functions: NULLIF and COALESCE, so their use is preferable (if the specific DBMS implements them).
See also
- [[b154]]
- [[b5783]]
- [[b9118]]
See also
Comments