SQL BETWEEN
The BETWEEN operator selects values within a data range between two values.
SQL BETWEEN Operator
The BETWEEN operator selects values in the data range between two values. These values can be numeric, text, or dates.
SQL BETWEEN Syntax
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Demo Database
In this tutorial we will use php Sample database.
The following is the data selected from the "Websites" table:
| id | name | url --------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | Taobao | https://www.taobao.com/ | 13 | CN |
| 3 | php Chinese website | //m.sbmmt.com/ | 4689 | CN |
| 4 | Weibo | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+----------- ----------------+-------+---------+
BETWEEN operator ExampleThe following SQL statement selects all websites with alexa between 1 and 20:
NOT BETWEEN operator example
To display websites that are not within the scope of the above example, please use NOT BETWEEN:
Example
WHERE alexa NOT BETWEEN 1 AND 20;
Execution output result:
With BETWEEN operator example with IN
The following SQL statement selects all websites where alexa is between 1 and 20 but country is not USA and IND:
Example
WHERE (alexa BETWEEN 1 AND 20)
AND NOT country IN ('USA', 'IND');
Execution output result:
With text value BETWEEN Operator Example
The following SQL statement selects all websites whose name starts with a letter between 'A' and 'H':
Example
WHERE name BETWEEN 'A' AND 'H';
Execution output result:
Example of NOT BETWEEN operator with text value
The following SQL statement selects all websites whose name does not start with a letter between 'A' and 'H':
Example
WHERE name NOT BETWEEN 'A' AND 'H';
Execution output result:
Sample table
The following is the data of the "access_log" website access record table, among which:
aid: is the self-increasing id.
site_id: is the website id corresponding to the websites table.
#count: Number of visits.
date: is the access date.
+-----+---------+-------+ ------------+
| aid | site_id | count | date |
+-----+---------+----- --+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------ +
9 rows in set (0.00 sec)
The access_log table SQL file used in this tutorial: access_log.sql.
BETWEEN operator example with date value
The following SQL statement selects date between '2016-05-10' and '2016-05-14' All access records between:
Instance
WHERE date BETWEEN '2016-05-10' AND '2016-05-14';
Execution output result:
 | Please note that in different databases, the BETWEEN operator will produce different results! So please check how your database handles the BETWEEN operator! |
|---|
