SQL BETWEEN operator

The BETWEEN operator is used in the WHERE clause to select a data range between two values.

BETWEEN operator

The operator BETWEEN ... AND selects a data range between two values. These values can be numeric, text, or dates.

SQL BETWEEN syntax

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Original Table (used in the example):

Persons Table:

Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing
4 Gates Bill Xuanwumen 10 Beijing

BETWEEN Operator Example

If you want to display people between "Adams" (including) and "Carter" (excluding) in alphabetical order, please use the following SQL:

SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter'

Result Set:

Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York

Important Note:Different databases handle the BETWEEN...AND operator differently. Some databases may list people between "Adams" and "Carter", but not including "Adams" and "Carter"; some databases may list people between "Adams" and "Carter" including "Adams" and "Carter"; and some databases may list people between "Adams" and "Carter" including "Adams", but not including "Carter".

So, please check how your database handles the BETWEEN....AND operator!

Example 2

If you need to display people outside the range of the above example, please use the NOT operator:

SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Adams' AND 'Carter'

Result Set:

Id LastName FirstName Address City
3 Carter Thomas Changan Street Beijing
4 Gates Bill Xuanwumen 10 Beijing