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 |
+-----+-----------+------------+-------------------+-----------+--------+
Geek University 2022