|
When you define a query, you can use ordinary operators such as >, >=, =, <, <=. Also, you can use the NOT, and the OR operator, which are represented by the exclamation point (!) and pipe symbol (|) respectively, as described in the following table. Further, you can use the wildcards that are specified in the second table.
Special Operators
Operator
|
Purpose
|
! (NOT operator)
|
The exclamation point reverses the meaning of your query condition. For example, !200 means "not equal to 200", !J%S means "not LIKE J%S".
|
NULL
|
The query condition <column> IS NULL is added.
|
!NULL
|
The query condition <column> IS NOT NULL is added.
|
| (i.e. OR)
|
The pipe symbol specifies an OR condition between fields. For example, the last name of an instructor must begin with a letter "larger" than A, or the (first) name must be John.
The pipe symbol must be typed in EACH field to which the OR applies. The resulting OR condition is then ANDed with the other query conditions, if any.
|
BETWEEN
|
BETWEEN specifies the range to be queried. For example:
BETWEEN 20 AND 30
retrieves all records that have a value in the range 20 to 30 for the column concerned.
!BETWEEN 20 and 30
retrieves all records that have a value OUTSIDE the range 20 to 30 for the column concerned.
|
Within one query, you can use any number of query conditions. They are implicitly connected by the AND operator, except for conditions that use the pipe symbol, which are connected by the OR operator.
NOTE 1: First all the AND conditions are connected, then the OR conditions are added. For example:
A and B or C and D
Is NOT parsed as:
(A and B) or (C and D)
but as:
(A and B and D) or C
NOTE 2: An extra query (virtual) is treated as if it involves ONE column, even if it consists of a composite key.
NOTE 3: OR conditions on tables containing joined columns while in Query mode work correctly. However, if you do not specify at least one AND condition, it will lead to unexpected results. In most cases if there are only OR conditions specified, then one of them will be added as an AND condition to avoid full table scans.
Using Wildcards in Queries
Wildcard
|
Purpose
|
_
|
An underscore matches any single character. For example, JA_KY matches "Jacky" but not "Jabberwocky".
|
%
|
A percent sign matches zero or more characters. For example, J%Y matches "Joy", "Jacky", and "Jabberwocky".
|
=
|
When used for string values, the equals sign removes the special significance of _ and %. Use it if you are actually looking for the underscore or percent sign. For example, =J%K will only match "J%K".
|
|
