Comparison operators are used with character (including Memo), numeric, or date/time data and can be used in the WHERE or HAVING clause of a query. Comparison operators evaluate to a Boolean data type; they return TRUE, FALSE or NULL based on the outcome of the tested condition.
The comparison operators supported by Absolute Database are listed in the table below:
|
Operator
|
Description
|
Examples
|
=
|
Equal
|
Name = 'Smith'
|
<>
|
Not equal to
|
State <> 'CA'
|
>
|
Greater than
|
BillDate > '01 Jan 2002'
|
<
|
Less than
|
Price < 95.5
|
>=
|
Greater than or equal to
|
ID >= 100
|
<=
|
Less than or equal to
|
Code <= 'T'
|
BETWEEN expr1 AND expr2
|
Tests range of values
|
BETWEEN
'01 Jan 1995'
AND
'31 Dec 1995'
|
[NOT] IN (list | subquery)
|
Tests whether column is equal to any member in list
|
Color IN ('Blue', 'Red', 'Green')
|
IS [NOT] NULL
|
Tests whether contents of column is null
|
Fax IS NULL
|
[NOT] LIKE pattern [ESCAPE escape_char]
|
Performs pattern matching ( % means any string of zero or more characters; _ (underscore)
means any single character ). Escape character is used to search for a % or a _ character.
|
Name LIKE 'John%'; CustNo LIKE '100_'; Progress LIKE '%0\%' ESCAPE '\'
|
ANY (SOME)
|
Tests whether one or more rows in the result set of a subquery meet the specified condition.
|
Color = ANY (subquery)
|
ALL
|
Tests whether all rows in the result set of a subquery meet the specified condition.
|
Salary > ALL (subquery)
|
[NOT] EXISTS
|
Tests whether a subquery returns any results.
|
EXISTS (subquery)
|
|
__________________________
Absolute SQL Reference - 05 January 2023, Niels Knabe