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.
The syntax for the BETWEEN operator has been illustrated with a SQL SELECT Statement.
SELECT col_1, col_2, col_3 ...
WHERE col_x BETWEEN starting_value AND ending_value;
The above query will select the columns specified with
col_3, etc. from
table_name for which the values of
col_x lies between the
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.
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.
SELECT * FROM employees WHERE emp_no BETWEEN 10035 AND 10043
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.
SELECT * FROM employees WHERE emp_no NOT BETWEEN 10035 AND 10043
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.
SELECT * FROM employees WHERE first_name BETWEEN 'Kyoichi' AND 'Prasadram'
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
SELECT * FROM employees WHERE first_name NOT BETWEEN 'Kyoichi' AND 'Prasadram'
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.
SELECT * FROM employees WHERE emp_no BETWEEN 10001 AND 10100 AND YEAR(birth_date) IN (1959,1960,1961)
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.
SELECT * FROM employees WHERE hire_date BETWEEN '1989-05-01' AND '1990-05-01'
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.
- PHP MySQL CRUD Tutorial For Beginners
- Rename A Column In MySQL
- Import SQL File (MySQL Command Line & GUI)
Also, don’t forget to Subscribe to WTMatter!