Comparison Operators

<< Click to Display Table of Contents >>

Navigation:  SQL Reference > Functions and Operators >

Comparison Operators

Previous pageReturn to chapter overviewNext page

 

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 - 25 May 2022, Niels Knabe