You can use any standard SQL operators in a search condition. When you use operators in a search condition, the following rules apply:
name >= 'M'
), and some of the rows contain no value, those rows do not appear, no matter what comparison operator you use.The following table summarizes search condition operators that are defined for standard SQL.
Note The information in this topic is derived from the rules for standard SQL-92. However, each database can implement SQL in its own way. Therefore, the guidelines provided here might not apply in every case. If you have questions about how to use operators with a particular database, refer to the documentation for the ODBC driver or database that you are using.
Operator | Meaning | Grid pane example1 | SQL pane example |
= | Equal |
|
|
<> != |
Not equal to |
|
|
> | Greater than |
|
|
< | Less than |
|
|
>= !< |
Greater than or equal to |
|
|
<= !> |
Less than or equal to |
|
|
BETWEEN expr1 AND expr2 |
Tests range of values |
|
|
IS [NOT] NULL | Tests whether contents of column or result of expression is null |
|
|
[NOT] LIKE | Performs pattern matching (usually restricted to character data types) |
|
|
expr1 [NOT] IN (val1, val2, …) – or – expr1 [NOT] IN (subquery) |
Matches list of specific values by testing whether expr1 appears either in a list of values or in the result set of a subquery |
|
|
ANY (SOME) | Tests whether one or more rows in the result set of a subquery meet the specified condition. (ANY and SOME are synonyms; the Query Designer will use ANY when creating an SQL statement.) |
|
|
ALL | Tests whether all rows in the result set of a subquery meet the specified condition |
|
|
[NOT] EXISTS | Tests whether a subquery returns any results whatsoever (not a specific result) |
|
|
1For clarity, the Grid pane examples include only one example for each operator and do not indicate which data column is being searched.
2Dates can be entered in the Grid pane using the format specified in the Windows Regional Settings dialog box. For details, see Entering Search Values.