SAND CDBMS SQL Reference Guide
Nulls

A Null represents an unknown or missing value in a column. Nulls are NOT equivalent to zero, blank spaces, or the empty string. In fact, no two nulls are equivalent.

WHERE

When specifying the null value in the selection criteria of a WHERE clause, the proper syntax is WHERE x IS NULL (or, to exclude null values, WHERE x IS NOT NULL).

GROUP BY

For grouping purposes within a query, all nulls are assigned to the same group.

ORDER BY

For ordering purposes within a query, all nulls are treated as equivalent values. Nulls always appear at the end of an ORDER BY, regardless of whether ascending or descending order is specified.

DISTINCT

For the purpose of SELECT DISTINCT queries, all nulls are treated as equivalent values.

COUNT

The aggregate function COUNT (*) includes nulls when counting the number of rows in the output table, unless a WHERE clause specifically excludes null values. The function COUNT (DISTINCT column) excludes nulls when counting the number of distinct values in a column. The function COUNT (ALL column), or simply COUNT (column), excludes nulls when counting the total number of values in a column.

Null Assignments

The following SQL UPDATE statement illustrates the assignment of null values to an entire column:

UPDATE president SET byear = NULL;

A WHERE clause may be added in order to assign null values to a column only for selected rows. SAND CDBMS assigns nulls automatically to columns that are omitted from an INSERT statement on a particular table.

Null Arithmetic

Any arithmetic expression that contains a null value will evaluate to null. That is, where x is any numeric or null value:

x + null = null + x = null
x - null = null - x = null
x * null = null * x = null
x / null = null / x = null

For example, consider the following SQL fragment:

CREATE TABLE t1 (col1 INT, col2 INT);
INSERT INTO t1 VALUES (10, NULL);
INSERT INTO t1 VALUES (5, 20);
.NULLS *****
SELECT * FROM t1;

COL1        COL2
----------- -----------
         10       *****
          5          20

UPDATE t1 SET col1 = col1 * col2;
SELECT * FROM t1;

COL1        COL2
----------- -----------
      *****       *****
        100          20

The UPDATE statement replaces the value of col1 with the product of col1 and col2 for each row. In the first row, since col2 is null, the product of col1 and col2 is also null. Therefore, col1 is assigned a null value.

Note: The .NULLS system command causes the nisqlm client program to display null values as the user-defined string that follows the .NULLS keyword. Consult the SAND CDBMS Tools Reference Guide for more information about nisqlm system commands.