SQL IN & BETWEEN operators

In this tutorial, you will learn how to use the IN and BETWEEN operators and use them with the WHERE clause.

Using range and membership conditions

In the previous chapter, we learned how to combine multiple conditions using the AND and OR operators. However, sometimes this is not enough, for example, if you must check values that are within a range or a set of values.

Here, the IN and BETWEEN operators appear, which allow you to define an exclusive range or a set of values instead of combining separate conditions.

IN operator

The IN operator is a logical operator used to check if a specific value exists within a set of values. Its basic syntax can be given in the following way:

SELECT column_list FROM table_name
WHERE column_name IN (value)1, value1,...);

Here,column_listis the column of the database table from which you want to retrieve the value/fieldNameFor examplename,age,countryAlright, let's look at some examples.

etc.employeesConsider that in our database we have a

+--------+--------------+------------+--------+---------+
|　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 statement will only returndept_idFor1or3Those employees.

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

After executing the query, you will obtain the following result set:

+--------+--------------+------------+--------+---------+
|　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 opposite of the IN operator. The following SQL statement will return all employees except those mentioned above.dept_idis not1or3All employees except those mentioned above.

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

After executing the query, this time you will obtain the following result set:

+--------+--------------+------------+--------+---------+
|　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　|
+--------+--------------+------------+--------+---------+

BETWEEN operator

If the value in a column falls within a specific range, sometimes you want to select a row. This type of condition is common when dealing with numeric data.

To execute a query based on this condition, you can use the BETWEEN operator. It is a logical operator that allows you to specify the range to be tested, as shown below:

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

Let's base our query onEmployees (employees)Build and execute a query based on the range conditions on the table.

Define a numeric range

The following SQL statement will only returnemployeesThe salaries in7000 to9000 among those employees.

SELECT　*　FROM employees WHERE salary BETWEEN　7000 AND　9000;

After execution, you will get the following output:

+--------+--------------+------------+--------+---------+
|　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 a date range

When using the BETWEEN operator with date or time values, please use the CAST() function to explicitly convert these values to the required data type for the best results. For example, if comparing with DATE using such strings as " 2016-12-31Such strings are converted to DATE as shown below:

The following SQL statement selects2006Year1Month1Day (i.e., " 2006-01-01") to2016Year12Month31Day (i.e., " 2016-12-31All employees hired between the dates '

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 obtain the following result set:

+--------+--------------+------------+--------+---------+
|　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 Range

Although date and number ranges are the most common, you can also set conditions to search for string ranges. 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 get the following output:

+--------+--------------+------------+--------+---------+
|　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　|
+--------+--------------+------------+--------+---------+