The SQL BETWEEN operator is used along with WHERE clause for providing a range of values. The values can be the numeric value, text value, and date.

SQL BETWEEN operator is almost like SQL IN operators used in a sequential manner.

The values are defined as part of the BETWEEN range are inclusive i.e. the values that are mentioned in the range are included at the start and end values.

Let’s discuss in detail about the BETWEEN operator.

As mentioned above BETWEEN operator can be used along with numeric value, text value, and date. We will discuss all the three in detail below.

SQL Between Syntax

Using the above-mentioned syntax, we can define values as part of BETWEEN operator. Also, the syntax mentioned above remains the same for usage with a numeric value, text value, and date value.

SQL BETWEEN operator for Numeric value

We will understand the above-mentioned syntax in more detail through some examples for numeric value.

Let’s consider the following Student table for example purpose.

RollNo StudentName StudentGender StudentAge StudentPercent AdmissionDate
1 George M 14 85 2018-01-01
2 Monica F 12 88 2018-01-31
3 Jessica F 13 84 2018-01-15
4 Tom M 11 78 2017-12-15

I am using MySQL database and here is the script to create and insert example records in the Student table.

Scenario: Get the percentage of students whose age is between 11 and 13.

Output:

SQL Between Clause example

SQL NOT BETWEEN Operator for Numeric Value

The SQL NOT BETWEEN operator is used for getting the values as part of result set which is outside of the range specified by the BETWEEN operator.

Scenario: Get the percentage of students whose age is not between 11 and 13.

Output:

sql not between example

SQL BETWEEN operator for Text value

Scenario: Get the RollNo, StudentName and StudentAge where StudentName is between George and Jessica.

Output:

RollNo StudentName StudentAge
1 George 14
3 Jessica 13

SQL NOT BETWEEN Operator for Text Value

Scenario: Get the RollNo, StudentName and StudentAge where StudentName is not between George and Jessica.

Output:

RollNo StudentName StudentAge
2 Monica 12
4 Tom 11

SQL BETWEEN operator for Date value

Scenario: Get the age of students whose admission is between 1st Jan 2018 and 31st Jan 2018.

Output:

Note that I am using MySQL native function str_to_date to convert string to date. If the string is in default format, we can use it as-is too, just as I have used for the second argument.

If you are using Oracle DB, then corresponding function is TO_DATE.

SQL Between Date Example

SQL NOT BETWEEN Operator for Date Value

Scenario: Get the age of students whose admission is not between 1st Jan 2018 and 31st Jan 2018.

Output:

MULTIPLE BETWEEN operators

We can use multiple between operators too. Its syntax is:

Using the above-mentioned syntax, we can use multiple BETWEEN operators.

Scenario: Get the student name with age between 10 and 13 and marks between 80 to 85 percentage.

Output:
SQL Multiple Between Operators

By admin

Leave a Reply