Equality Comparison Predicates
The following are the available comparison predicates:
SQL Equality Comparison Predicates
| Predicate |
Operation |
| = |
Equals |
| <> |
Does not equal |
| != |
Does not equal |
| > |
Is greater than |
| < |
Is less than |
| >= |
Is greater than or equal to |
| <= |
Is less than or equal to |
The following example uses a comparison predicate. It returns one record for each Age less than 21:
SELECT Name, Age FROM Sample.Person
GROUP BY Age
HAVING Age < 21
ORDER BY Age
Note that SQL defines comparison operations in terms of collation: the order in which values are sorted. Two values are equal if they collate in exactly the same way. A value is greater than another value if it collates after the second value. String data type field collation is based on the field’s default collation. By default, it is not case-sensitive. Thus, a comparison of two string field values or a comparison of a string field value with a string literal is (by default) not case-sensitive. For example, if Home_State field values are uppercase two-letter strings:
| Expression |
Value |
| 'MA' = Home_State |
TRUE for values MA. |
| 'ma' = Home_State |
TRUE for values MA. |
| 'VA' < Home_State |
TRUE for values VT, WA, WI, WV, WY. |
| 'ar' >= Home_State |
TRUE for values AK, AL, AR. |
Note, however, that a comparison of two literal strings is case-sensitive: WHERE 'ma'='MA' is always FALSE.
IN and %INLIST Predicates
The IN predicate is used for matching a value to an unstructured series of items.
The %INLIST predicate is an InterSystems IRIS extension for matching a value to the elements of a list structure.
With either predicate you can perform equality comparisons and subquery comparisons.
IN has two formats. The first serves as shorthand for the use of multiple equality comparisons linked together with the OR operator. For instance:
SELECT Name, Home_State FROM Sample.Person
GROUP BY Home_State
HAVING Home_State IN ('ME','NH','VT','MA','RI','CT')
evaluates true if Home_State equals any of the values inside the parenthetical list. The list elements can be constants or expressions. Collation applies to the IN comparison as it applies to an equality test. By default, IN comparisons use the collation type of the field definition; by default string fields are defined as SQLUPPER, which is not case-sensitive.
When dates or times are used for IN predicate equality comparisons, the appropriate data type conversions are automatically performed. If the HAVING clause field is type TimeStamp, values of type Date or Time are converted to Timestamp. If the HAVING clause field is type Date, values of type TimeStamp or String are converted to Date. If the HAVING clause field is type Time, values of type TimeStamp or String are converted to Time.
The following examples both perform the same equality comparisons and return the same data. The GROUP BY field specifies to return only one record for each successful equality comparison. The DOB field is of data type Date:
SELECT Name,DOB FROM Sample.Person
GROUP BY DOB
HAVING DOB IN ({d '1951-02-02'},{d '1987-02-28'})
SELECT Name,DOB FROM Sample.Person
GROUP BY DOB
HAVING DOB IN ({ts '1951-02-02 02:37:00'},{ts '1987-02-28 16:58:10'})
For further details refer to Date and Time Constructs.
The %INLIST predicate can be used to perform an equality comparison on the elements of a list structure. %INLIST uses EXACT collation. Therefore, by default, %INLIST string comparisons are case-sensitive. For further details on list structures, see the SQL $LIST function.
The following example uses %INLIST to match a string value to the elements of the FavoriteColors list field:
SELECT Name,FavoriteColors FROM Sample.Person
HAVING 'Red' %INLIST FavoriteColors
It returns all records where FavoriteColors includes the element “Red”.
The following example matches Home_State column values to the elements of the northne (northern New England states) list:
SELECT Name,Home_State
FROM Sample.Person
HAVING Home_State %INLIST $LISTBUILD("VT","NH","ME")
You can also use IN or %INLIST with a subquery to test whether a column value (or any other expression) equals any of the subquery row values. For example:
SELECT Name,Home_State FROM Sample.Person
HAVING Name IN
(SELECT Name FROM Sample.Employee
HAVING Salary < 50000)
Note that the subquery must have exactly one item in the SELECT list.
For further details, refer to the IN and %INLIST reference pages in this manual.
