Comparison Operators

See Also      Tasks

You can use any standard SQL operators in a search condition. When you use operators in a search condition, the following rules apply:

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 
= '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'2
 
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' 2
 
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’2


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 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)

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.