Comparison Operators

Overview of search condition operators

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

Summary of search condition operators

The following table summarizes search condition operators that are defined for standard SQL.

Operator Meaning Grid 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'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_nameFROM 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)