SQL BETWEEN: Searching Between Two Specified Values (Range)

Syntax

expression [ NOT ] BETWEEN lower_value AND higher_value

The SQL BETWEEN Operator are used to display rows between two specified values. The lower value are placed before AND operator and the higher value are placed after AND operator.

For Example you have a table called Articles that contain the following Data:

ArticleID Title                     PageView    PublishDate
--------- ------------------------- ----------- -------------------
1         SQL JOIN Example                   43 2008-06-04 15:18:00
2         SQL BETWEEN Example                84 2008-06-07 15:23:00
3         SQL Where Example                   0 2008-06-07 15:27:00
4         SQL: Find Duplicate Values        102 2008-05-29 15:30:00
5         SQL HAVING Example                 50 2008-03-19 15:33:00

Using SQL Between on Numeric Data Type Field

The following SQL Statement example will return Articles that having PageView Between 50 and 100:


Select ArticleID,Title,PageView,PublishDate

from Articles

where PageView Between 50 and 100


Query Result:

ArticleID Title                     PageView    PublishDate
--------- ------------------------- ----------- -------------------
2         SQL BETWEEN Example                84 2008-06-07 15:23:00
5         SQL HAVING Example                 50 2008-03-19 15:33:00

Using SQL Between on Date Data Type Field

The following SQL Statement example will return Articles published between 29th May 2008 and 8th June 2008 :


Select ArticleID,Title,PageView,PublishDate

from articles

where PublishDate between '29-may-2008' and '08-jun-2008'


Query Result

ArticleID Title                     PageView    PublishDate
--------- ------------------------- ----------- -------------------
1         SQL JOIN Example                   43 2008-06-04 15:18:00
2         SQL BETWEEN Example                84 2008-06-07 15:23:00
3         SQL Where Example                   0 2008-06-07 15:27:00
4         SQL: Find Duplicate Values        102 2008-05-29 15:30:00

you can use SQL Statement bellow if you work with a MS Access Database. it will produce the same result


Select ArticleID,Title,PageView,PublishDate

from articles

where PublishDate between #01/06/2008# and #08/06/2008#

To display rows outside the range you specified, you must omits NOT operator before BETWEEN operator. Example :


Select ArticleID,Title,PageView,PublishDate

from Articles

where PageView Not Between 50 and 100


Query Result

ArticleID Title                     PageView    PublishDate
--------- ------------------------- ----------- -------------------
1         SQL JOIN Example                   43 2008-06-04 15:18:00
3         SQL Where Example                   0 2008-06-07 15:27:00
4         SQL: Find Duplicate Values        102 2008-05-29 15:30:00