IN and BETWEEN Conditional
Operators
SELECT column1, SUM(column2) FROM "list-of-tables" WHERE
column3 IN (list-of-values);
SELECT column1, SUM(column2) FROM "list-of-tables" WHERE
column3 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.
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.
Click the exercise answers link below if you have any problems.
Answers to
these Exercises
Enter SQL Statement
here:
|