SQL BETWEEN Operator

Introduction to MySQL BETWEEN Operator

Before learning about BETWEEN in MySQL, let us first get a brief introduction to SQL and Database Management Systems. SQL (Structured Query Language) is used to manipulate and communicate with the data stored as tables in the databases. To manipulate the data, we first need to insert the data into the tables. A SQL query is used to request records from the database's tables.

The BETWEEN operator in MySQL, or in short "BETWEEN", is a logical operator that is used to select a range of values from the database table. Using BETWEEN, we can also check whether a value is in the provided range or not. Between query in MySQL is generally used with the SELECT statements. It can also be used with INSERT, DELETE, and UPDATE queries.

Syntax of MySQL BETWEEN

The syntax of between query in MySQL is very simple. We just need to provide the range (starting and ending values) that the BETWEEN operator uses to retrieve the records from the database tables.

Syntax:

• expression: Expression specifies a column.
• first_value, second value: These values are used to define the range for validating the expression. The range is inclusive.

Parameters of MySQL BETWEEN

The between query in MySQL takes an expression as the parameter or argument. The expression can be a column or a calculation. This expression with parameter will be used to validate the records, and if a record is within the range of the values, then the record will be returned.

What does the BETWEEN in MySQL returns?

As we know, BETWEEN in MySQL is a logical operator, so BETWEEN operator first validates whether a record is within the provided range or not. After the validation, the records within the given range will be returned.

MySQL BETWEEN condition with Numeric Value

So far, we have seen the syntax, parameters, and return values of between query in MySQL. Let us take an example of numeric values to understand the working of the BETWEEN operator.

Output:

Since $25$ is within the range of $1$ and $50$, therefore true is returned. True is termed as $1$ in MySQL. So, the output came out to be $1$.

Let us take a real-life example of the student database. The database has a table named students. The student’s table has four columns, namely student ID, Name, Physics (marks in Physics), and Chemistry (marks in Chemistry).

Overview of the student’s table:

Suppose we want to select the names of the student(s) whose marks in Physics range from 75 to 95. The MySQL query for the same will be:

Output:

BETWEEN condition With Date

Let us take an example of another student table to see how between query in MySQL works with dates in MySQL.

Suppose the database has a table named students. The student’s table has three columns: student ID, Name, and Joined (which tells us the date when the student joined).

Overview of the student’s table:

Now, suppose we want to select the names of the student(s) who joined the college between 12 August 2016 to 12 August 2022. The MySQL query for the same will be:

Output:

BETWEEN condition With Text Values

So far, we have seen how numeric values are provided as ranges in between query in MySQL. For example, text values are used as ranges in the BETWEEN clause. Suppose the database has a table named employees. The employee’s table has three columns: employee ID, Name, and Salary.

Overview of the employee’s table:

Now, suppose we want to select the names and salaries of the employees whose names start with any letter between D and S. The MySQL query for the same will be:

Output:

Here, Sushant and Saumya also lie in the specified range but BETWEEN in MySQL will only match with S but not with strings (names) starting with S. So, to get the names starting with S. We need to use a tilde (~) symbol after the second value.

We can also write the same query as:

Output:

Using NOT Operator

We can reverse the working of between query in MySQL using the NOT operator. We can combine the NOT operator with the BETWEEN operator to exclude the records that lie in the provided range or values. Let's use an example to understand the working of NOT BETWEEN.

Suppose we are working with the database of students. The database has a table named students. The student’s table has $4$ columns, namely student ID, Name, Physics (marks in Physics), and Chemistry (marks in Chemistry).

Overview of the student’s table:

Now, suppose we want to select the names of the student(s) whose marks in Physics do not lie in the range of $75$ to $95$. The MySQL query for the same will be:

Output:

That was all about SQL between operators, also, have a look at this SQL Tutorial to learn more about SQL in detail.