SQL IN & BETWEEN

Working with Range and Membership Conditions

In the previous chapter, we learned how to combine multiple conditions using the AND and OR operators. However, this might not always be sufficient and very efficient, for example, when you need to check values within a range or set of values.

This is where the IN and BETWEEN operators come into play, allowing you to define an exclusive range or a set of values instead of combining separate conditions.

The IN Operator

The IN operator is a logical operator used to check whether a specific value exists within a set of values. Its basic syntax is:

Here, column_list represents the names of columns or fields such as name, age, country, etc., in a database table whose values you want to fetch. Let's look at some examples.

Suppose we have an employees table in our database with the following records:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
+--------+--------------+------------+--------+---------+

The following SQL query will fetch only those employees whose dept_id is either 1 or 3.

SELECT * FROM employees
WHERE dept_id IN (1, 3);

After running the query, you will get a result set similar to this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
+--------+--------------+------------+--------+---------+

Similarly, you can use the NOT IN operator, which is the exact opposite of the IN. The following SQL statement will return all employees except those whose dept_id is 1 or 3.

SELECT * FROM employees
WHERE dept_id NOT IN (1, 3);

After executing the query, this time you will receive a result set similar to this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
+--------+--------------+------------+--------+---------+

The BETWEEN Operator

Sometimes you need to select a row if the value in a column falls within a specific range. This type of condition is common when dealing with numeric data.

To query based on such a condition, you can use the BETWEEN operator. It's a logical operator that allows you to specify a range to test, as follows:

SELECT column1_name, column2_name, columnN_name
FROM table_name
WHERE column_name BETWEEN min_value AND max_value;

Let's construct and execute queries based on range conditions in our employees table.

Define Numeric Ranges

The following SQL statement will retrieve employees from the employees table whose salary falls within the range of 7000 and 9000.

SELECT * FROM employees 
WHERE salary BETWEEN 7000 AND 9000;

After execution, you will receive output similar to this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
+--------+--------------+------------+--------+---------+

Define Date Ranges

When using the BETWEEN operator with date or time values, it's recommended to use the CAST() function to explicitly convert values to the desired data type for accurate comparisons. For instance, when comparing a string like '2016-12-31' to a DATE, cast the string to a DATE, as shown below:

The following SQL statement selects all employees hired between January 1, 2006 (i.e., '2006-01-01') and December 31, 2016 (i.e., '2016-12-31'):

SELECT * FROM employees WHERE hire_date
BETWEEN CAST('2006-01-01' AS DATE) AND CAST('2016-12-31' AS DATE);

After executing the query, you will receive a result set similar to this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
+--------+--------------+------------+--------+---------+

Define String Ranges

While ranges of dates and numbers are common, you can also create conditions to search for ranges of strings. The following SQL statement selects all employees whose names start with any letter between 'O' and 'Z':

SELECT * FROM employees
WHERE emp_name BETWEEN 'O' AND 'Z';

After execution, you will receive output similar to this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
+--------+--------------+------------+--------+---------+