BETWEEN x AND y

You can retrieve data from each row whose column, named in a WHERE clause, has a value within two limits. Use BETWEEN in place of an AND condition when using greater than or equal to (>=) and less than or equal to (<=).

The limits you specify are inclusive. Enter the lower boundary (smaller value) of the BETWEEN condition first, then the upper boundary (larger value). The following example selects employees who have a salary between $20,000 and $21,000. GRAHAM has a salary of exactly $21,000.

This query:

SELECT ID, NAME, SALARY
FROM Q.STAFF
WHERE SALARY BETWEEN 20000 AND 21000

Produces this report:

  ID  NAME          SALARY
----- ---------  ---------
  50  HANES       20659.80
 210  LU          20010.00
 310  GRAHAM      21000.00

Examples:

• Select everyone whose name is alphabetically between HANES and MOLINARE:

  SELECT ID, NAME
  FROM Q.STAFF
  WHERE NAME BETWEEN 'HANES' AND 'MOLINARE'

• Select everyone who has between 10 and 12 years of service (inclusive):

  SELECT ID, NAME, YEARS
  FROM Q.STAFF
  WHERE YEARS BETWEEN 10 AND 12

• Select employees whose salary is NOT in the range of $19,000 to $21,000:

  SELECT ID, NAME, YEARS
  FROM Q.STAFF
  WHERE SALARY NOT BETWEEN 19000 AND 21000

  Each employee whose salary is less than $19,000 or more than $21,000 is included in the report. Employees with salaries between and including $19,000 and $21,000 are not included.