Comparison Operators
The comparison operators can be loosely grouped into equality comparisons and range comparisons. The basic equality comparison operators, in precedence order, are ==, =, >, >=, <, <=, !=, and <>. The == comparison and the = comparison are treated as the same operator and produce the same results.
If these binary operators are applied to two operands of different types, AMPS attempts to convert strings to numbers. If conversion succeeds, AMPS uses the numeric values. If conversion fails because the string cannot be meaningfully converted to a number, strings are always considered to be greater than numbers. The operators consider an empty string to be NULL.
The following table shows some examples of how AMPS compares different types.
1 < 2
TRUE
10 < '2'
FALSE, '2' can be converted to a number
'2.000' <> '2.0'
TRUE, no conversion to numbers since both are strings
2 = 2.0
TRUE, numeric comparison
10 < 'Crank It Up'
TRUE, strings are greater than numbers
10 < ''
FALSE, an empty string is considered to be NULL
10 > ''
FALSE, an empty string is considered to be NULL
'' = ''
FALSE, an empty string is considered to be NULL
'' IS NULL
TRUE, an empty string is considered to be NULL
There are also set and range comparison operators. The BETWEEN operator can be used to check the range values.
The range used in the BETWEEN operator is inclusive of both operands, meaning the expression /A BETWEEN 0 AND 100 is equivalent to /A >= 0 AND /A <= 100.
For example:
The IN operator can be used to perform membership operations on sets of values. The IN operator returns true when the value on the left of the IN appears in the set of values in the IN clause. For example:
The IN operator returns true for the set of records that would be returned by an equivalent set of = comparisons joined by OR. The following two statements return the same set of records:
This equivalence means that NULL values in either the field being evaluated, or the set of values provided to the IN clause, always return false.
This also means that, for string values, the IN operator performs exact, case-sensitive matching.
When using NOT IN, AMPS interprets this as a NOT unary operator applied to the IN operator. This means that the following expressions are equivalent:
When evaluating against a set of values, the IN operator typically provides better performance than using a set of OR operators. That is, a filter written as /firstName IN ('Joe', 'Kathleen', 'Frank', 'Cindy', 'Mortimer') will typically perform better than an equivalent filter written as /firstName = 'Joe' OR /firstName = 'Kathleen' OR /firstName = 'Frank' OR /firstName = 'Cindy' OR /firstName = 'Mortimer'.
Last updated