SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-78
Defining Columns That Allow or Prohibit Null
Null in DISTINCT, GROUP BY, and ORDER BY Clauses
In evaluating the DISTINCT, GROUP BY, and ORDER BY clauses, NonStop SQL/MX
considers all nulls to be equal. Additional considerations for these clauses are:
Null and Expression Evaluation Comparison
DISTINCT Nulls are considered duplicates; therefore, a result has at most one
null.
GROUP BY The result has at most one null group.
ORDER BY Nulls are considered greater than nonnull values.
Expression Type Condition Result
Boolean operators (AND,
OR, NOT)
Either operand is null. For AND, the result is null.
For OR, the result is true if the
other operand is true, or null if
the other operand is null or false.
For NOT, the result is null.
Arithmetic operators Either or both operands are
null.
The result is null.
NULL predicate The operand is null. The result is true.
Aggregate (or set)
functions
(except COUNT)
Some rows have null
columns.The function is
evaluated after eliminating
nulls.
The result is null if set is empty.
COUNT(*) The function does not
eliminate nulls.
The result is the number of rows
in the table whether or not the
rows are null.
COUNT
COUNT DISTINCT
The function is evaluated
after eliminating nulls.
The result is zero if set is empty.
Comparison: =, <>,
<, >, <=, >=, LIKE
Either operand is null. The result is null.
IN predicate Some expressions in the IN
value list are null.
The result is null if all of the
expressions are null.
Subquery No rows are returned. The result is null.