NULL Operator

The NULL operator in an SQL statement represents a value that is not known or is not applicable. Siebel CRM evaluates an expression that includes a NULL operator differently than it evaluates other operators. NULL is not a value. A comparison function does not operate correctly if a NULL operator exists in the comparison. For instance, if NULL = NULL is not TRUE. Note the following:

• SQL and Siebel CRM provide special functions and grammar that support NULL, including the IS NULL operator and the IfNull function. A comparison, string concatenation, and Boolean operation include special behavior that handles a NULL operator.

• You can set the type for a NULL operator similar to how you set the type for a value. An operand or result can be a NULL string, NULL number, NULL Boolean, and so on.

• If one side of a comparison is:

  • NULL. The comparison returns a NULL of type Boolean.

  • Is not NULL. The comparison returns TRUE or FALSE. For example, 1>2 is FALSE, and 1<NULL is NULL.

• If one side of an arithmetic operation is NULL, then the operation returns NULL of the appropriate type, except for a string concatenation. NULL adds no characters during a string concatenation operation. For example:

  • 1 + 2 is 3

  • 1 + NULL is NULL (of type Integer)

  • "Fred" + ", Smith" is "Fred, Smith"

  • "Fred" + NULL is "Fred"