IN & BETWEEN

SELECT col1, SUM(col2) 

FROM "list-of-tables" 

WHERE col3 IN 
(list-of-values);

SELECT col1, SUM(col2) 

FROM "list-of-tables" 

WHERE col3 BETWEEN value1 
AND value2;

The IN conditional operator is really a set membership test operator. That is, it is used to test whether or not a value (stated before the keyword IN) is “in” the list of values provided after the keyword IN.

For example:

SELECT employeeid, lastname, salary

FROM employee_info

WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');

This statement will select the employeeid, lastname, salary from the employee_info table where the lastname is equal to either: Hernandez, Jones, Roberts, or Ruiz. It will return the rows if it is ANY of these values.

 
The IN conditional operator can be rewritten by using compound conditions using the equals operator and combining it with OR – with exact same output results:

SELECT employeeid, lastname, salary 

FROM employee_info 

WHERE lastname = 'Hernandez' OR lastname = 'Jones' OR lastname = 'Roberts' 
OR lastname = 'Ruiz';

As you can see, the IN operator is much shorter and easier to read when you are testing for more than two or three values.

You can also use NOT IN to exclude the rows in your list.

 
The BETWEEN conditional operator is used to test to see whether or not a value (stated before the keyword BETWEEN) is “between” the two values stated after the keyword BETWEEN.

For example:

SELECT employeeid, age, lastname, salary 

FROM employee_info 

WHERE age BETWEEN 30 AND 40;

This statement will select the employeeid, age, lastname, and salary from the employee_info table where the age is between 30 and 40 (including 30 and 40).

This statement can also be rewritten without the BETWEEN operator:

SELECT employeeid, age, lastname, salary 

FROM employee_info 

WHERE age >= 30 AND age <= 40;

You can also use NOT BETWEEN to exclude the values between your range.

Review Exercises

1. Select the date, item, and price from the items_ordered table for all of the rows that have a price value ranging from 10.00 to 80.00.

   Show Answer

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

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

   Show Answer

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

Results will be displayed here