Comparison Operators

  • 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. For information about converting data types, see Data Type Conversion.

  • 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 and how the operators are entered in the Grid Pane. For more information, see Operators.

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)

1    For clarity, the Grid pane examples include only one example for each operator and do not indicate which data column is being searched.
2    Dates can be entered in the Grid pane using the format specified in the Windows Regional Settings dialog box. For details, see Entering Search Values.

See Also

Reference