SQL IN & BETWEEN Operators

SQL IN and BETWEEN logical operators can be used to define a range of allowed values on a WHERE clause to filter records on a database table.

  • These are generally used along with the WHERE and HAVING clauses that define conditions to filter records.
  • The IN operator can be used to define a set of values.
  • The BETWEEN operator can be used to define a range of values, which includes all the values within the range and the values as well.
  • Commonly used on queries like SELECT, UPDATE, DELETE, etc.,

In the previous chapters, we have seen how to filter records using AND and OR operators.

Now, let's see how to filter records using IN and BETWEEN logical operators along with the WHERE clause.

Syntax

Here is the basic syntax for the IN operator on queries SELECT, UPDATE, and DELETE.

SELECT * FROM table_name
WHERE column_name IN (value1, value1,...);
UPDATE table_name 
SET column_name = value 
WHERE column_name IN (value1, value2, ...);
DELETE FROM table_name 
WHERE column_name IN (value1, value2, ...);

Here is the basic syntax for the BETWEEN operator on queries SELECT, UPDATE, and DELETE.

SELECT * FROM table_name 
WHERE column_name BETWEEN min_value AND max_value;
UPDATE table_name 
SET column = value 
WHERE column_name BETWEEN min_value AND max_value;
DELETE FROM table_name 
WHERE column_name BETWEEN min_value AND max_value;

Table Data

Let's assume the below data on the table employees before executing any of the below queries.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

IN Operator

Use the below example to retrieve the employees having employee IDs in the list (1, 2, 3), where the ID list is defined using IN operator.

Run this on IDE

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

After successful execution, the output looks like this, which includes the records satisfying the condition.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

NOT IN Operator

Use the below example to retrieve the employees not having employee IDs in the list (1, 2, 3), where the ID list is defined using IN operator.

Run this on IDE

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

After successful execution, the output looks like this, which includes the records satisfying the condition.

employee_id employee_name gender birth_date hire_date salary
2 Tony Blank M 1982-08-07 2010-01-05 89000
4 James Cooper M 2000-10-20 2015-05-10 45000

BETWEEN Operator for Numeric Range

Use the below example to retrieve employees having salaries between 10000 and 30000, where the salary range is defined using BETWEEN operator.

Run this on IDE

SELECT * FROM employees 
WHERE salary BETWEEN 10000 AND 30000;

After successful execution, the output looks like this, which includes the records satisfying the condition.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
3 Andrew Russel M 1998-05-04 2012-02-20 28000

BETWEEN Operator for Date Range

Use the below example to retrieve employees, born between 1st January 1990 (i.e. 1990-01-01) to 1st January 2020 (i.e. 2020-01-01), where the date range is defined using BETWEEN operator.

  • For date and time values, use the CAST() function to explicitly convert the values to the desired data type for better results.

Run this on IDE

SELECT * FROM employees 
WHERE birth_date BETWEEN CAST('1990-01-01' AS DATE) AND CAST('2020-01-01' AS DATE);

After successful execution, the output looks like this, which includes the records satisfying the condition.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-05-10 45000

BETWEEN Operator for String Range

While the numeric and date ranges are most common, we can also use a string range to specify a character range.

Use the below example to retrieve employees, with names beginning with any of the letters between 'A' to 'K', where the string range is defined using the BETWEEN operator.

Run this on IDE

SELECT * FROM employees 
WHERE employee_name BETWEEN 'A' AND 'K';

After successful execution, the output looks like this, which includes the records satisfying the condition.

employee_id employee_name gender birth_date hire_date salary
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-05-10 45000

Other Statements

In the above examples, we have used the IN and BETWEEN operators on SELECT statements.

Similarly, these operators can be used on UPDATE and DELETE statements.

Overall

We now know how to combine conditions using IN and BETWEEN operators on the WHERE clause.

Related Links