SQL BETWEEN operator
The SQL BETWEEN operator is used to check for a range of values. The syntax:
SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
We will use the following table in our example:
+-----+-----------+-------------+-------------------+-----------+--------+
| No | LAST NAME | FIRST NAME | CITY | COUNTRY | AMOUNT |
+-----+-----------+-------------+-------------------+-----------+--------+
| 168 | Franco | Keith | New Haven | USA | 1286 |
| 169 | de Castro | Isabel | Lisboa | Portugal | 2000 |
| 171 | Rancé | Martine | Lille | France | 1370 |
| 172 | Bertrand | Marie | Paris | France | 1337 |
| 173 | Tseng | Jerry | Cambridge | USA | 1188 |
| 175 | King | Julie | Bridgewater | USA | 2200 |
| 177 | Kentary | Mory | Kita-ku | Japan | 3200 |
| 181 | Frick | Michael | NYC | USA | 1286 |
| 186 | Karttunen | Matti | Helsinki | Finland | 1501 |
| 187 | Ashworth | Rachel | Manchester | UK | 1501 |
| 189 | Cassidy | Dean | Dublin | Ireland | 3400 |
| 198 | Taylor | Leslie | Brickhaven | USA | 1216 |
| 201 | Devon | Elizabeth | Liverpool | UK | 1501 |
| 202 | Tamuri | Yoshi | Vancouver | Canada | 3300 |
| 204 | Barajas | Miguel | Brickhaven | USA | 1188 |
| 205 | Young | Julie | Pasadena | USA | 1166 |
| 206 | Walker | Brydey | Singapore | Singapore | 5200 |
| 209 | Citeaux | Frédérique | Strasbourg | France | 4100 |
| 211 | Gao | Mike | Central Hong Kong | Hong Kong | 1621 |
| 216 | Saavedra | Eduardo | Barcelona | Spain | 3100 |
| 219 | Young | Mary | Glendale | USA | 1166 |
| 223 | Kloss | Horst | Cunewalde | Germany | 2900 |
| 227 | Ibsen | Palle | ?rhus | Denmark | 1401 |
| 233 | Fresni?re | Jean | Montréal | Canada | 1286 |
| 237 | Camino | Alejandra | Madrid | Spain | NULL |
| 239 | Thompson | Valarie | San Diego | USA | 1166 |
| 240 | Bennett | Helen | Cowes | UK | 1501 |
+-----+-----------+-------------+-------------------+-----------+--------+
Here is how we can use the SQL BETWEEN operator to retrieve all customers that have spent an amount between $1200 and $1900:
SELECT * FROM customers WHERE amount BETWEEN 1200 AND 1900;
This will produce the following result:
+-----+-----------+------------+-------------------+-----------+--------+
| No | LAST NAME | FIRST NAME | CITY | COUNTRY | AMOUNT |
+-----+-----------+------------+-------------------+-----------+--------+
| 168 | Franco | Keith | New Haven | USA | 1286 |
| 171 | Rancé | Martine | Lille | France | 1370 |
| 172 | Bertrand | Marie | Paris | France | 1337 |
| 181 | Frick | Michael | NYC | USA | 1286 |
| 186 | Karttunen | Matti | Helsinki | Finland | 1501 |
| 187 | Ashworth | Rachel | Manchester | UK | 1501 |
| 198 | Taylor | Leslie | Brickhaven | USA | 1216 |
| 201 | Devon | Elizabeth | Liverpool | UK | 1501 |
| 211 | Gao | Mike | Central Hong Kong | Hong Kong | 1621 |
| 227 | Ibsen | Palle | ?rhus | Denmark | 1401 |
| 233 | Fresni?re | Jean | Montréal | Canada | 1286 |
| 240 | Bennett | Helen | Cowes | UK | 1501 |
+-----+-----------+------------+-------------------+-----------+--------+
