Comparison Operators (Visual Database Tools)

  • Article

You can use any standard SQL operators in a search condition.

When you use operators in a search condition, the following rules apply:

  • The data types of the data used in the comparison must match. That is, only text can be compared to text, numbers to numbers, and so on. Some database management systems allow you to use a function or keyword (such as CAST) to temporarily convert data to another data type. For details, refer to the documentation for your database.

  • If you compare text data, the result depends on the character set currently in use. For example, if a table was created using Scandinavian characters, the search results might differ depending on whether your current character set (code page) is Scandinavian or another character set.

  • If a comparison value is null, the result is unknown. Nulls are not matched to any value, including other instances of null. For example, if you are searching for a name beginning with the letter "M" or higher (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 database that you are using.

Operator

Meaning

Criteria Pane example*

SQL pane example

=

Equal

 
= 'Smith'
 
SELECT fname, lname
FROM employees
WHERE lname = 'Smith'

<> !=

Not equal to

 
<> 'Active'
 
SELECT fname, lname
FROM employees
WHERE status <> 'Active'

>

Greater than

 
> '01 Jan 1995'**
 
SELECT fname, lname
FROM employees
WHERE hire_date >
 '12/31/90'

<

Less than

 
< 100
 
SELECT fname, lname
FROM employees
WHERE job_lvl < 100

>= !<

Greater than or equal to

 
>= 'T'
 
SELECT au_lname
FROM authors
WHERE au_lname >= 'T'

<= !>

Less than or equal to

 
<= '01 Jan 1995'**
 
SELECT fname, lname
FROM employees
WHERE hire_date <= 
 '01/01/95'

BETWEEN expr1 AND expr2

Tests range of values

 
BETWEEN 
'01 Jan 1995' 
AND 
'31 Dec 1995'**
 
SELECT fname, lname
FROM employees
WHERE hire_date 
 BETWEEN '12/31/90'
 AND '12/31/91'

IS [NOT] NULL

Tests whether contents of column or result of expression is null

 
IS NULL
 
SELECT fname, lname
FROM employees
WHERE photo_on_file IS NULL

[NOT] LIKE

Performs pattern matching (usually restricted to character data types)

 
LIKE ('MAC%')
 
SELECT fname, lname
FROM employees
WHERE lname LIKE ('MAC%')

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

 
IN ('SW', 'SE')
supplier_id IN
  (subquery)
 
SELECT fname, lname
FROM employees
WHERE sales_region IN ('SW', 'SE')
SELECT product_name
FROM products
WHERE supplier_id IN
  (SELECT supplier_id
  FROM supplier
  WHERE (country = 'Sweden'))

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 and View Designer will use ANY when creating an SQL statement.)

 
<> ANY (subquery)
 
SELECT au_lname, au_fname
FROM authors
where city <> any
 (SELECT city FROM publishers)

ALL

Tests whether all rows in the result set of a subquery meet the specified condition

 
advance > ALL (subquery)
 
SELECT title FROM titles
where advance > all
  (SELECT advance FROM
   publishers,titles
  where titles.pub_id 
   = publishers.pub_id
  AND pub_name = 
   'Alogdata Infosystems')

[NOT] EXISTS

Tests whether a subquery returns any results whatsoever (not a specific result)

 
EXISTS (subquery)
 
SELECT product_name
FROM products
WHERE EXISTS
  (SELECT * FROM 
   orders, products
  WHERE orders.prod_id 
  = products.prod_id)

*   For clarity, the Criteria Pane examples include only one example for each operator and do not indicate which data column is being searched.

**   Dates can be entered in the Criteria pane using the format specified in the Windows Regional Settings dialog box. For details, see Rules for Entering Search Values.

See Also

Other Resources

Designing Queries and Views

Reference (Visual Database Tools)