Rules for comparing values

Since you can compare values of different type in query language, rules for comparing two values have been defined. These rules are used for:

• comparing values in comparison operators;
• defining the maximum and minimum values in the aggregate functions MIN and MAX;
• ordering query result records according to the order given in the clause ORDER BY.

If value types differ from each other, then the relationship between values is defined based on type priority:

• NULL type (the lowest);
• Boolean type;
• Number type;
• DATE type;
• STRING type;
• reference types.

The relationship between different reference types is defined based on internal table reference numbers corresponding to one type or another.

If the data are of the same type, then their values are compared according to the following rules:

• the Boolean type TRUE value is greater than FALSE;
• the NUMBER type has standard rules of number comparison;
• the DATE type earlier dates are less than later dates;
• for STRING type comparison is performed according to the established national features of the database;
• reference types are compared based on their values (entry number etc.);
• comparison of fields with unlimited length is not supported (strings of unlimited length, ValueStorage, VALUETYPE field from the charts of characteristic types table).

Important! Any comparison of two values that includes at least one NULL value returns a result equivalent to FALSE.

 

See also: Logical Expressions