A
Anonymous
Guest
To understand this problem, you must realize that "x BETWEEN a AND b" is really a mathematical expression that is equivalent to "x >= a AND x <= b". Now, imagine you have a table like this:
If you do the query "SELECT name FROM test1 WHERE name BETWEEN 'F' AND 'S';", what MySQL does is compare each of the names to the values 'F' and 'S'. If it's both greater than 'F' and less than 'S', it will select the value. When it gets to "Stella", it will see that, aphabetically "Stella" comes after, and therefore, it is not BETWEEN 'F' AND 'S', so it will return the following result set:
Now, if you want to include "Stella", the best way to do it is just use the next letter. So, instead of "BETWEEN 'F' AND 'S'", just do "BETWEEN 'F' AND 'T'". The only place this fails is if you have someone named 'T', but you can easily add a != 'T' clause.
Code:
+-----------+
| name |
+-----------+
| Albert |
| Ferdinand |
| Jordan |
| Mary |
| Stella |
| Trogdor |
+-----------+If you do the query "SELECT name FROM test1 WHERE name BETWEEN 'F' AND 'S';", what MySQL does is compare each of the names to the values 'F' and 'S'. If it's both greater than 'F' and less than 'S', it will select the value. When it gets to "Stella", it will see that, aphabetically "Stella" comes after, and therefore, it is not BETWEEN 'F' AND 'S', so it will return the following result set:
Code:
mysql> SELECT * FROM test1 WHERE name BETWEEN 'F' AND 'S';
+-----------+
| name |
+-----------+
| Ferdinand |
| Jordan |
| Mary |
+-----------+
3 rows in set (0.00 sec)Now, if you want to include "Stella", the best way to do it is just use the next letter. So, instead of "BETWEEN 'F' AND 'S'", just do "BETWEEN 'F' AND 'T'". The only place this fails is if you have someone named 'T', but you can easily add a != 'T' clause.