MySQL BETWEEN

Summary: In this chapter, we will learn how to use MySQL BETWEEN operator to determine whether selects values within a range.

MySQL BETWEEN Operator

Introduction to MySQL BETWEEN Operator

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates. We often use the BETWEEN operator in the WHERE clause.

The following illustrates the syntax of the BETWEEN operator:

1 2 3 4 5 6

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

Above syntax to test in the range that is defined by value1 and value2.

In case you want to specify an exclusive range, you use the greater than (>) and less than (<) operators.

MySQL BETWEEN examples

We will continue with the employees table data in the sample database as shown in the picture below.

BETWEEN operator:

Below example selects all data within range only using BETWEEN operator.

Suppose we want to find employees whose salary scale between 25000 to 35000. we can use the BETWEEN operator as the following query:

1 2 3 4 5 6 7

SELECT firstname,lastname, jobtitle, salary FROM employees WHERE salary BETWEEN 25000 AND 35000;

We can achieve the same result by using the greater than or equal (>=) and less than or equal ( <= ) operators as the following query:

1 2 3 4 5 6

SELECT firstname,lastname, jobtitle, salary FROM employees WHERE salary >=25000 AND salary <=35000;

NOT BETWEEN operator

This example selects all data out of range using NOT BETWEEN operator.

To display the products outside the range of the previous example, use NOT BETWEEN:

1 2 3 4 5 6

SELECT firstname,lastname, jobtitle, salary FROM employees WHERE salary NOT BETWEEN 25000 AND 35000;

we can rewrite the query above using the less than (>), greater than (>), and logical operators (OR) as the following query.

1 2 3 4 5 6

SELECT firstname,lastname, jobtitle, salary FROM employees WHERE salary < 25000 OR salary > 35000;

MySQL BETWEEN with dates example

When you use the BETWEEN operator with date values, to get the best result, you should use the type cast to explicitly convert the type of column or expression to the DATE type.

For example, to get the employees whose born from 01/01/1989 to 01/12/1991, we use the following query:

1 2 3 4 5 6 7

SELECT firstname, lastname, dob, jobtitle, salary FROM employees WHERE dob BETWEEN CAST('1989-01-01' AS DATE) AND CAST('1991-12-31' AS DATE);

Because the data type of the required date column is DATE so we used the cast operator to convert the literal strings ‘1989-01-01’ and ‘1991-12-31’ to the DATE data type.

In this chapter, we have learnt how to use the BETWEEN operator to test if a value falls within a range or out of range values.

---

MySQL IN MySQL LIKE

---