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.

Image 025.jpgUsing BETWEEN

BETWEEN tests whether a value falls within a range. The BETWEEN operator is case-sensitive and does not accept wildcards.

  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 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 keyword NOT to negate BETWEEN.
    SELECT last_name,
           first_name
    FROM   Employee
    WHERE  last_name BETWEEN 'C' AND 'H'
    

    The image below shows the expected result:

    TutorialWhere13.png
  2. 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'
    
  3. Execute the following query to retrieve names of employees whose salaries are between 62000 and 98000 (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:

    TutorialWhere14.png

Image 025.jpgUsing 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.

  1. 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 image below shows the expected result:

    TutorialWhere15.png

Advance To: