SQL Reference Absolute Database > SQL Comparison operators

Comparisons

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	Example
=	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)	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	Performs pattern matching.	Name LIKE 'John%'
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)

* Note: Komparative operatorer som f.eks. <> eller < anvendt overfor felter, der kan have "værdien" NULL vil ikke inkludere poster, hvor feltet har værdien NULL, da NULL angiver manglende data og derfor heller ikke lader sig evaluere og dermed heller ikke kan siges at opfylde et givent komparativt kriterie.
For inklusion af poster med felter af NULL-værdi, skal man derfor bruge:
WHERE felt IS NULL

** Note: For referencer til bestemte datoer anbefales det at anvende SQL ISO formatet 'yyyy-MM-dd', der altid vil forstås eentydigt uafhængigt af den lokale Windows system-opsætning. F.eks.:
WHERE dato_felt > '2005-01-22'
eller
WHERE dato_felt BETWEEN '2005-01-23' AND '2005-04-22'

_____________________________

27-10-2017 - Niels Knabe