InterBase Quick Start: Part IV - Comparing Against a Range or List of Values
Go Up to InterBase Quick Start: Part IV - Using the WHERE Clause
The previous sections present comparison operators that test against a single value. The BETWEEN and IN operators test against multiple values.
BETWEEN tests whether a value falls within a range. The BETWEEN operator is case-sensitive and does not require wildcards.
Finding Something BETWEEN Values
1. Execute the following query to find all the last names that start with letters between C and H. Notice that the query does not include names that begin with the final value (“H”). This is because BETWEEN finds values that are less than or equal to the terminating value. A name that begins with the letter but includes other letters is greater than H. If there were someone whose last name was just “H”, the query would return it.
SELECT last_name, first_name FROM Employee
WHERE last_name BETWEEN 'C' AND 'H'
- The result set is:
2. To demonstrate that BETWEEN is case sensitive, repeat the previous query using lower-case letters. There are no names returned.
SELECT last_name, first_name FROM Employee
WHERE last_name BETWEEN 'c' AND 'h'
3. Execute the following query to retrieve names of employees whose salaries are between $62,000 and $98,000, inclusive:
SELECT last_name, first_name, salary FROM Employee
WHERE salary BETWEEN 62000 AND 98000
ORDER BY salary
The result set should return 13 rows, with salaries that include both the low figure and the high figure in the range. For more information on the ORDER BY clause, see Using ORDER BY to Arrange Rows.
Finding What’s IN
The IN operator searches for values matching one of the values in a list. The values in the list must be separated by commas, and the list must be enclosed in parentheses. Use NOT IN to search for values that do not occur in a set.
Execute the following query to retrieve the names of all employees in departments 120, 600, and 623:
SELECT dept_no, last_name, first_name FROM Employee
WHERE dept_no IN (120, 600, 623)
ORDER BY dept_no, last_name
The returns the following result set:
