SQL BETWEEN Operator

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

Overview

BETWEEN in SQL is a logical operator that is used to select a range of values from the database table. The BETWEEN operator first validates whether a record lies in the provided range. After the validation, it returns the records that lie within the given range.

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.

Note: A logical operator is used to perform boolean operations (TRUE or FALSE) on the operands or the two data values present in the database tables. The logical operators return True if both the operands follow the logical condition.

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.

Note: What is the inclusive range?

An inclusive range is one where the limits are also included. For example, a survey of “10-20 years old inclusive range" means 10 and 20-year-olds were counted, too.

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.

Note: In MySQL, a boolean True is treated as 11 and a boolean False is treated as 00.

Let us take a few examples to understand BETWEEN in MySQL.

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 2525 is within the range of 11 and 5050, therefore true is returned. True is termed as 11 in MySQL. So, the output came out to be 11.

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:

IDNamePhysicsChemistry
1Aman8692
2Sushant9191
3Saumya9898
4Kausiki7676
5Aditya6767

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:

Note: The above query is similar to saying WHERE Physics >= 75 AND Physics <= 95.

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:

IDNameJoined
1Aman2019-01-05
2Sushant2016-08-12
3Saumya2016-08-12
4Kausiki2012-11-19
5Aditya2022-10-21

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:

IDNameSalary
1Aman86000
2Sushant91000
3Saumya98000
4Kausiki76000
5S67000

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 44 columns, namely student ID, Name, Physics (marks in Physics), and Chemistry (marks in Chemistry).

Overview of the student’s table:

IDNamePhysicsChemistry
1Aman8692
2Sushant9191
3Saurabh9898
4Kausiki7676
5Aditya6767

Now, suppose we want to select the names of the student(s) whose marks in Physics do not lie in the range of 7575 to 9595. 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.

Conclusion

  • BETWEEN is a logical operator in SQL used to select a range of values from the database table. Using the between the query, we can also check whether a value is in the provided range or not.
  • BETWEEN is generally used with SELECT statements and with INSERT, DELETE, and UPDATE queries.
  • The syntax of BETWEEN clause is: expression BETWEEN first_value AND second_value.
  • BETWEEN operator first validates whether a record lies in the provided range or not. After the validation, the records that lie within the given range are returned.
  • 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.