SQL BETWEEN Operator

SQL BETWEEN Operator

This is a detailed tutorial on SQL BETWEEN Operator. Learn how to filter values that lie between a range using the BETWEEN Operator in the WHERE Clause.

Use of SQL Between Operator

SQL Between Operator is used within the SQL WHERE clause to filter those records for which particular column values lie between a certain range specified with this operator. BETWEEN Operator is always used along with another operator, the AND operator. The value of ranges can be numerical, textual or even date and time type values. This way, the BETWEEN operator, provides numerous query search possibilities. Some of these are mentioned below.

  • To find records that are created between a specific period of time.
  • To find records whose column names lie between a textual alphabetical range.
  • To find records that have numerical IDs between the certain numerical range.
  • And many more.

One more thing to notice, BETWEEN operator, works in an inclusive way, which means the starting and ending values that you specify in this operator are also meant to be included in the result set.

BETWEEN Syntax

The syntax for the BETWEEN operator has been illustrated with a SQL SELECT Statement.

The above query will select the columns specified with col_1, col_2, and col_3, etc. from table_name for which the values of col_x lies between the starting_value and ending_value.

SQL BETWEEN Examples

A number of examples have been given below to illustrate the different use cases of SQL BETWEEN Operator and also along with some of the other associated SQL Operators.

The example queries are run on a demo database table employees. A quick look at this database table is shown in the following screenshot.

Demo Employees Database Table

BETWEEN Numerical Values

This is the simplest example, we always follow this way to select records that lies between a certain numerical range. The following query will select all the records for which the value of the column emp_no lies between 10035 and 10043.

SQL BETWEEN Operator Simple Example

NOT BETWEEN Numerical Values

The following example illustrates the use of NOT and BETWEEN operators together. We’ll run the same query of the above example but with just the addition of NOT Keyword before the BETWEEN operator. Thus, this way it will select all the records for which the value of the column emp_no does not lie between 10035 and 10043.

SQL NOT BETWEEN Example

BETWEEN Textual Values

You can also select records that lie between a certain range of textual values. The following query illustrates the same. It will fetch the records for which the value of the column first_name lies with Kyoichi and Prasadram.

SQL BETWEEN Textual Values Example

NOT BETWEEN Textual Values

Similar to numerical values, you can also use the BETWEEN and NOT operator together for textual values also. The following query is the same query like the above example with the addition of NOT operator. So, it will fetch all records that do not lie between the textual values Kyoichi and Prasadram for the column first_name.

SQL NOT BETWEEN Textual Values Example

BETWEEN & IN Operator

IN Operator is not used directly with BETWEEN operator but it can be used in the same WHERE clause in which the BETWEEN condition has been specified. The following query illustrates such a usage of the BETWEEN and IN operator.

This SQL query will select all the records of the employees table for which the emp_no lies between 10001 and 10100 and also the year of birth_date is either 1959, or 1960 or 1961.

SQL BETWEEN AND IN Operator Example

Filtering BETWEEN Dates

You can also filter between date and time values using the SQL BETWEEN Operator. In the following query, we’re fetching all the records for which the hire_date lies between 1989-05-01 and 1990-05-01.

SQL BETWEEN DATE TIME Example

Note. You’ve to write Date or Time in quotes in the query.

Further, you can make use of the BETWEEN operator in a lot more useful queries and also with other SQL Operators.

I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.

Helpful Associated Guides.

Also, don’t forget to Subscribe to WTMatter!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

Your email address will not be published. Required fields are marked *