InterBase Quick Start: Part IV - Comparing Against a Range or List of Values
From InterBase
Go Up to InterBase Quick Start: Part IV - Using the WHERE Clause
The BETWEEN
and IN
comparison operators test against multiple values.
Using BETWEEN
BETWEEN
tests whether a value falls within a range. The BETWEEN
operator is case-sensitive and does not accept wildcards.
- Execute the following query to find all the last names that start with letters between
C
andH
. Notice that the query does not include names that begin with the final value (H
). This is becauseBETWEEN
finds values that are less than or equal to the terminating value. A name that begins with a letter but includes other letters is greater than the letter. For example,BETWEEN
treats a string'Ha'
as greater than the character'H'
. You can use the keywordNOT
to negateBETWEEN
.SELECT last_name, first_name FROM Employee WHERE last_name BETWEEN 'C' AND 'H'
The image below shows the expected result:
- To confirm that
BETWEEN
is case sensitive, repeat the previous query using lower-case letters. You get an empty result.SELECT last_name, first_name FROM Employee WHERE last_name BETWEEN 'c' AND 'h'
- Execute the following query to retrieve names of employees whose salaries are between
62000
and98000
(inclusive):SELECT last_name, first_name, salary FROM Employee WHERE salary BETWEEN 62000 AND 98000 ORDER BY salary
The result returns 13 rows, with salaries that include both the low figure and the high figure in the range.
The image below shows the expected result:
Using IN
The IN
operator searches for values matching one of the values in a list. You must separate the values in the list by commas, and the list must be enclosed in parentheses. Use NOT IN
to search for values that do not occur in the list.
- Execute the following query to retrieve the names of all employees in departments
120
,600
, and623
:SELECT dept_no, last_name, first_name FROM Employee WHERE dept_no IN ( 120, 600, 623 ) ORDER BY dept_no, last_name
The image below shows the expected result: