SQL: BETWEEN … AND…

The Between keyword allows you to define a predicate in the form of a range. If a column value for a row falls within this range, then the predicate is true and the row will be added in the result table. The Between range test consists of two keywords, Between and AND. It must be supplied with the upper and the lower range values. The first value must be lower bound and the second value, the upper bound.

The syntax for the Between clause is as follows:

SELECT column_name 
FROM derived_table 
WHERE column_name [NOT] BETWEEN 'value1' AND 'value2' 

This will retrieve all rows whose column has a value between ‘value1’ and ‘value2’. The Between clause can be used in any valid SQL statement – select, insert, update, or delete. It’s important to notice that it can be treated differently in different databases.

Example #1

SELECT EmployeeIDNo, Salary 
FROM EmployeeStatisticsTable 
WHERE Salary BETWEEN 30000 AND 50000 

This query would return all employee ids whose Salary is between 30000 and 50000, inclusive.

Example #2

SELECT EmployeeIDNo, Salary 
FROM EmployeeStatisticsTable 
WHERE Salary NOT BETWEEN 30000 AND 50000 

This query uses the NOT operator to search for values that lie outside the range specified.

Example #3

SELECT * 
FROM EmployeeAddressTable 
WHERE LastName BETWEEN 'Ackerman' AND 'Scott' 

Retrieve employees alphabetically between (and including) “Ackerman” and exclusive “Scott”.