|
|
|
A null represents an undefined or unknown value and is specified by the keyword null. A null is not the same as a zero, a blank, or an empty string. A null can be assigned to any nullable column when no other value is specifically assigned. More information about defining nullable columns is provided in the section Create Table in the chapter "Using SQL Statements."
The ifnull function and the is null predicate allow nulls in queries to be handled. For details, see ifNull function and isNull in the chapter "Elements of SQL Statements."
|
|
|
Because a null is not a value, it cannot be compared to any other value (including another null value). For example, the following where clause evaluates to false if one or both of the columns is null:
where columna = columnb
Similarly, the where clause:
where columna < 10 or columna >= 10
is true for all numeric values of columna, but false if columna is null.
|
|
|
If an aggregate function against a column that contains nulls is executed, the function ignores the nulls. This prevents unknown or inapplicable values from affecting the result of the aggregate. For example, if the aggregate function, avg(), is applied to a column that holds the ages of your employees, be sure that any ages that have not been entered in the table are not treated as zeros by the function. This distorts the true average age. If a null is assigned to any missing ages, the aggregate returns a correct result: the average of all known employee ages.
Aggregate functions, except count(), return null for an aggregate that has an argument that evaluates to an empty set. (Count() returns 0 for an empty set.) In the following example, the select returns null, because there are no rows in the table named test.
create table test (col1 integer not null);
select max(col1) as x from test;
In the above example, use the ifnull function to return a zero (0) instead of a null:
select ifnull(max(coll),0) as x from test;
For more information, see ifNull function in the chapter "Elements of SQL Statements."
When specifying a column that contains nulls as a grouping column (that is, in the group by clause) for an aggregate function, nulls in the column are treated as equal for the purposes of grouping. This is the one exception to the rule that nulls are not equal to other nulls. For information about the group by clause, see the chapter "Using SQL Statements".
|
|
|
When creating a table with nullable columns and subsequently creating integrities on those columns (using the create integrity statement), the constraint must include the or...is null clause to ensure that nulls are allowed in that column.
For example, if the following create table statement is issued:
create table test (a int, b int not null);
/* "a" is nullable */
and the following integrity constraint is defined on the test table:
create integrity on test is a > 10;
the comparison, a >10, is not true whenever a is null. For this reason, the table does not allow nulls in column a, even though the column is defined as a nullable column.
Similarly, the following insert statements fails:
insert into test (b) values (5);
insert into test values (null, 5);
Both of these insert statements are acceptable if the integrity had not been defined on column a. To allow nulls in column a, define the integrity as:
create integrity on test is a > 10 or a is null;
Note: If an integrity on a nullable column is created without specifying the or...is null clause and the column contains nulls, the DBMS Server issues an error and the integrity is not created.