NULL Keyword

The NULL keyword is valid in most contexts where you can specify a value. What it specifies, however, is the absence of any value (or an unknown or missing value).

NULL Keyword

NULL

Within SQL, the keyword NULL is the only syntactic mechanism for accessing a NULL value. NULL is not equivalent to zero, nor to any specific value. In ascending ORDER BY operations, NULL values precede any non-NULL value; in descending sorts, NULL values follow any non-NULL value. In GROUP BY operations, all NULL values are grouped together. (Such groups might in fact be logically heterogeneous, if they include missing or unknown values.)

The keyword NULL is a global symbol in the syntactic context of expressions, meaning that its scope of reference is global.

Every data type, whether built-in or user-defined, can represent a NULL value. HCL Informix® supports cast expressions in the projection list. This means that users can write expressions of the form NULL::datatype, in which datatype is any data type known to the database server.

HCL Informix® supports the typed NULL keyword in general expressions. NULL alone in these scenarios results in a -201 syntax error. As a result, if null is defined as a column name or a procedure name, it must be referenced with a table alias. Otherwise, it returns a -201 syntax error. The behavior is summarized in the following examples and results:

create table tab1 (a int, null int);
create table tab2 (a int, b int);
Table 1. NULL behavior
Statement Result 
select null from tab1 where a = 1
 -201 syntax error 
select * from tab1 where null = a
 -201 syntax error 
select * from tab1 where tab1.null = a
 Valid syntax 
select * from tab1 where a = null
 -201 syntax error 
select * from tab2 where a = null
 -201 syntax error 
select * from tab2 where null = a
 -201 syntax error 
select * from tab2 where null = a
 -201 syntax error 
select NULL::int from tab1
 Valid syntax 
select NULL::int from tab1
 Valid syntax 
select 1 + NULL::int from tab1
 Valid syntax 
select 1 + NULL::int from tab2
 Valid syntax 
select NULL::int + 1 from tab1
 Valid syntax

HCL Informix® prohibits the redefinition of NULL, because allowing such definition would restrict the global scope of the NULL keyword. For this reason, any mechanism that restricts the global scope or redefines the scope of the keyword NULL will syntactically disable any cast expression involving a NULL value. You must ensure that the occurrence of the keyword NULL receives its global scope in all expression contexts.

For example, consider the following SQL code: 
CREATE TABLE newtable
(
null int
);

SELECT null, null::int FROM newtable;

The CREATE TABLE statement is valid, because the column identifiers have a scope of reference that is restricted to the table definition; they can be accessed only within the scope of a table.

The SELECT statement in the example, however, poses some syntactic ambiguities. Does the identifier null appearing in the projection list refer to the global keyword NULL, or does it refer to the column identifier null that was declared in the CREATE TABLE statement?
  • If the identifier null is interpreted as the column name, the global scope of cast expressions with the NULL keyword will be restricted.
  • If the identifier null is interpreted as the NULL keyword, the SELECT statement must generate a syntactic error for the first occurrence of null because the NULL keyword can appear only as a cast expression in the projection list.
A SELECT statement of the following form is valid because the NULL column of newtable is qualified with the table name: 
SELECT newtable.null, null::int FROM newtable;
More involved syntactic ambiguities arise in the context of an SPL routine that has a variable named null. An example follows: 
CREATE FUNCTION nulltest() RETURNING INT;
   DEFINE a INT;
   DEFINE null INT;
   DEFINE b INT;
   LET a = 5;
   LET null = 7;
   LET b = null;
   RETURN b;
END FUNCTION;

EXECUTE FUNCTION nulltest();

When the preceding function executes in DB-Access, in the expressions of the LET statement, the identifier null is treated as the keyword NULL. The function returns a NULL value instead of 7.

Using null as a variable of an SPL routine would restrict the use of a NULL value in the body of the SPL routine. Therefore, the preceding SPL code is not valid, and causes HCL Informix® to return the following error: 
-947   Declaration of an SPL variable named 'null' conflicts
       with SQL NULL value.

In ESQL/C, you should use an indicator variable if there is the possibility that a SELECT statement will return a NULL value.