BETWEEN in SQL

Introduction to BETWEEN in SQL

BETWEEN is an expression operator generally used in the WHERE clause of a SQL INSERT, SELECT, DELETE and UPDATE query, that allows for specifying a range to test and filters the values or records within the given range while being inclusive and selecting both the begin and end values in the final result.

The SQL BETWEEN operator can be used for defining a specified range for Text, Numerical or data, and timestamp values. It is almost similar to an IN operator when used in a sequential manner. The counterpart of BETWEEN is NOT BETWEEN which does just the opposite. We will be discussing all the cases where BETWEEN can be used in detail.

Syntax and parameters

The basic syntax for writing SQL BETWEEN in WHERE clause is as follows:

SELECT column_name(s)
FROM table_name
WHERE test_expression { BETWEEN | NOT BETWEEN } begin_value AND end_value ;

The parameters used in the above syntax are :

SELECT column_name(s): It is used to select the required data from the database. Mention the column names that you want in the result set.

FROM table_name: Mention the table name or source from which the columns will be fetched.

WHERE: It is used to specify the conditions to filter records.

Test_expression: It is the expression for which we want to perform the test or the column name for which we want to specify the BETWEEN condition.

{ BETWEEN | NOT BETWEEN }: It is the expression operators that compare if the values are within the mentioned range or not.

Begin_value: The starting value of the range.

End_value: The last value of the range.

Of the above-mentioned parameters, all the parameters are mandatory. You may use GROUP

BY, ORDER BY and HAVING clauses based on your requirement.

Going ahead we will be discussing the above mentioned BETWEEN operator in great detail.

In order to understand the concept better, we will take the help of two tables, Employees (this contains personal details of all the employees) and departments (it contains details like department id, name, and its hod).

The data in the department’s table look something like this :

departmentid	departmentname	head
4001	Sales & Marketing	10024
4002	Products	10023
4003	Human Resources	10022

The data in the employee’s table is as follows:

employeeid	lastname	firstname	departmentid	address	city	create_dt	Salary
10028	Becker	Todd	4001	27 street	Oslo	2007-1-03	12000
10029	Rebecca	Ginny	4001	27 street	Manhattan	2007-12-03	12000
10027	Tobby	Riya	4002	31 street	Manhattan	2006-1-03	15000
10026	Sharma	Deepak	4002	10th street	New Delhi	2006-1-02	15000
10024	Krishna	Lina	4001	27 street	Oslo	2002-1-31	12000
10023	Jackson	David	4002	27 street	Manhattan	2001-12-31	15000
10022	Mayers	David	4003	27 street	Manhattan	2000-12-31	10000

Examples of BETWEEN in SQL

Here are the following examples mention below

Example #1 – With integer or numerical values

Find the names of the employees who earn a salary in the range of 12000 to 15000.

Code:

SELECT firstname,lastname
FROM employees
WHERE salary BETWEEN '12000' AND '15000';

In the above example, we tried to select only those employees whose salary lies in between 12000 and 15000. Since BETWEEN is inclusive in nature it filters the beginning i.e 12000 and ending values i.e 15000 as well.

Output:

Example #2 – With character/text values

Find the names of the employees, whose name starts with letters between ‘D’ and ‘G’.

Code:

SELECT firstname,lastname
FROM employees
WHERE firstname BETWEEN 'D' AND 'G';

Output:

Example #3 – With DATE values

Find the names of the employees, who joined the company between 2003 and 2006.

Code:

SELECT firstname,lastname, create_dt :: Date
FROM employees
WHERE create_dt :: Date BETWEEN '2003-01-01' AND '2006-12-31';

Output:

Example #4 – With IN operator

Find the names of the employees, who joined the company between 2003 and 2006 and are from Manhattan or New Delhi.

IN expression operator is very frequently used along with the BETWEEN operator in the WHERE clause. It is used to specify an additional condition in the query. IN filters only those rows/records which are present in the mentioned set.

Code:

SELECT firstname,lastname, create_dt :: Date, city
FROM employees
WHERE create_dt :: Date BETWEEN '2003-01-01' AND '2006-12-31'
AND city IN ('Manhattan', 'New Delhi');

Output:

Example #5

Find the names of the employees, who earn a salary between 12000 and 15000 and are also head of their department.

Code:

SELECT firstname,lastname, salary, departmentid
FROM employees
WHERE salary BETWEEN '12000' AND '15000'
AND employeeid IN (SELECT head::integer FROM department);

Output:

As the name suggests, NOT Between can be thought of as an expression operator that filters the values which do not belong to the specified range.

Example #6 – Using NOT BETWEEN expression operator

Find the details of the employees who do not earn a salary in the range of 12000 to 15000.

Code:

SELECT firstname,lastname, salary,departmentid
FROM employees
WHERE salary NOT BETWEEN '12000' AND '15000';

Output:

Using BETWEEN in the table join queries

Find the details of the employees along with the department name, who earn a salary in the range of 12000 to 15000.

Code:

SELECT e.employeeid, e.firstname, e.lastname, e.salary, d.departmentname
FROM employees as e INNER JOIN department as d
ON e.departmentid::integer = d.departmentid
WHERE e.salary BETWEEN '12000' AND '15000';

Output:

Example #7

Find the details of the employees along with the department name, who earn a salary in the range of 12000 to 15000 AND they work for the products department.

Code:

SELECT e.employeeid, e.firstname,e.lastname,e.salary, d.departmentname
FROM employees as e INNER JOIN department as d
ON e.departmentid::integer = d.departmentid
WHERE e.salary BETWEEN '12000' AND '15000' AND
d.departmentname ='Products' ;

Output:

Conclusion

In this article, we have learned that BETWEEN is an expression operator that is used to filter only those values which are in the specified range. It is inclusive in nature that means it selects both the beginning as well as ending values. It is mostly used in the WHERE clause part of the SQL query.

Recommended Articles

This is a guide to BETWEEN in SQL. Here we discuss the examples of BETWEEN in SQL along with the syntax and parameters. You may also look at the following articles to learn more –

1. SQL Cross Join
2. PostgreSQL ORDER BY
3. Primary Key in SQL
4. Left Outer Join in MySQL