Between... and
The between... and operator is used to select the range between two values. These values can be numbers, text, or dates.
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 |
-------------------------------------------
Original table (for example)
Lastname |
Firstname |
Address |
City |
Hansen |
OLA |
Timoteivn 10 |
Sandnes |
Nordmann |
Anna |
Neset 18 |
Sandnes |
Pettersen |
Kari |
Storgt 20 |
Stavanger |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
-------------------------------------------
Example 1
Use the following SQL statement to show people in alphabetical order (and include) "Hansen" and other than "pettersen:
SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen' |
Returned results:
Lastname |
Firstname |
Address |
City |
Hansen |
OLA |
Timoteivn 10 |
Sandnes |
Nordmann |
Anna |
Neset 18 |
Sandnes |
Important!The between... and operator is processed differently in different databases. In some databases, the person whose lastname is "Hansen" or "pettersen" is not displayed (between... and only select between and different regions that contain test values ). In some databases, the person whose lastname is "Hansen" or "pettersen" will be displayed (between... and select a region between and containing the test value ). In other databases, the person whose lastname is "Hansen" or "pettersen" is displayed, but "pettersen" is not displayed (.. and select between two values, including the first test value and the value other than the last test value ). Therefore, check how your database processes the between... and operator.
-------------------------------------------
Example 2
The not operator is used for persons out of the range in the previous example.
SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Hansen' AND 'Pettersen' |
Returned results:
Lastname |
Firstname |
Address |
City |
Pettersen |
Kari |
Storgt 20 |
Stavanger |
Svendson |
Tove |
Borgvn 23 |
Sandnes |