IN and BETWEEN Operators – WHERE Clause

An IN operator allows the specification of two or more expressions to be used for a query search. The result of the condition returns TRUE if the value of the corresponding column equals one of the expressions specified by the IN predicate.

Example 6.9 shows the use of the IN operator.

Get all the columns for every employee whose employee number equals 29346, 28559, or 25348:

USE sample;
SELECT emp_no, emp_fname, emp_lname
	FROM employee
	WHERE emp_no IN (29346, 28559, 25348);Code language: PHP (php)

The result is

An IN operator is equivalent to a series of conditions, connected with one or more OR operators. (The number of OR operators is equal to the number of expressions following the IN operator minus one.)

The IN operator can be used together with the Boolean operator NOT, as shown in Example 6.10. In this case, the query retrieves rows that do not include any of the listed values in the corresponding columns.

Get all columns for every employee whose employee number is neither 10102 nor 9031:

USE sample;
SELECT emp_no, emp_fname, emp_lname, dept_no
	FROM employee
	WHERE emp_no NOT IN (10102, 9031);Code language: PHP (php)

The result is

In contrast to the IN operator, which specifies each individual value, the BETWEEN operator specifies a range, which determines the lower and upper bounds of qualifying values. Example 6.11 provides an example.

Get the names and budgets for all projects with a budget between $95,000 and $120,000, inclusive:

USE sample;
SELECT project_name, budget
	FROM project
	WHERE budget BETWEEN 95000 AND 120000;Code language: PHP (php)

The result is

The BETWEEN operator searches for all values in the range inclusively; that is, qualifying values can be between or equal to the lower and upper boundary values. The BETWEEN operator is logically equal to two individual comparisons, which are connected with the Boolean operator AND. Example 6.11 is equivalent to Example 6.12.

USE sample;
SELECT project_name, budget
	FROM project
	WHERE budget >= 95000 AND budget <= 120000;Code language: PHP (php)

Like the BETWEEN operator, the NOT BETWEEN operator can be used to search for column values that do not fall within the specified range. The BETWEEN operator can also be applied to columns with character and date values. The two SELECT statements in Example 6.13 show a query that can be written in two different, but equivalent, ways.

Get the names of all projects with a budget less than $100,000 and greater than $150,000:

USE sample;
SELECT project_name
	FROM project
	WHERE budget NOT BETWEEN 100000 AND 150000;Code language: PHP (php)

The result is

Using comparison operators, the query looks different:

USE sample;
SELECT project_name
	FROM project
	WHERE budget < 100000 OR budget > 150000;Code language: PHP (php)