12.1. IN Operator

The IN operator is used to test whether a value is present in a list of values. It is a set membership test operator, meaning it checks if a specified value matches any value in a given list.

12.1.1 Syntax and Example

Basic syntax:

SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, ...);

Example:

SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');

This query selects the employeeid, lastname, and salary from the employee_info table where the lastname is either Hernandez, Jones, Roberts, or Ruiz.

12.1.2 Rewriting with OR

The IN operator can be rewritten using the = operator combined with OR:

SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname = 'Hernandez'
   OR lastname = 'Jones'
   OR lastname = 'Roberts'
   OR lastname = 'Ruiz';

The IN operator is more concise and readable for multiple values.

12.1.3 Using NOT IN

The NOT IN operator excludes rows that match the specified values:

SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname NOT IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');

12.2. BETWEEN Operator

The BETWEEN operator tests if a value falls within a specified range. It includes both boundary values.

12.2.1 Syntax and Example

Basic syntax:

SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example:

SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age BETWEEN 30 AND 40;

This query selects the employeeid, age, lastname, and salary from the employee_info table where the age is between 30 and 40 (inclusive).

12.2.2 Rewriting with Comparison Operators

The BETWEEN operator can be rewritten using >= and <=:

SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age >= 30 AND age <= 40;

12.2.3 Using NOT BETWEEN

The NOT BETWEEN operator excludes values within the specified range:

SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age NOT BETWEEN 30 AND 40;

12.3. Review Exercises & Answers

12.3.1. Exercise #1

Select the date, item, and price from the items_ordered table for all rows where the price ranges from 10.00 to 80.00.

SELECT order_date, item, price
FROM items_ordered
WHERE price BETWEEN 10.00 AND 80.00;

12.3.2. Exercise #2

Select the firstname, city, and state from the customers table for all rows where the state is either Arizona, Washington, Oklahoma, Colorado, or Hawaii.

SELECT firstname, city, state
FROM customers
WHERE state IN ('Arizona', 'Washington', 'Oklahoma', 'Colorado', 'Hawaii');