The comparison operators (including < and >) "work" with string values as well as numbers.
For MySQL
By default, string comparisons are not case sensitive and use the current character set. The default is latin1 (cp1252 West European), which also works well for English.
String comparisons will be case sensitive when the characterset collation of the strings being compared is case sensitive, i.e. the name of the character set ends in _cs rather than _ci. There's really no point in repeating all of the information that's available in MySQL Reference Manual here.
MySQL Comparison Operators Reference:
http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html
More information about MySQL charactersets/collations:
http://dev.mysql.com/doc/refman/5.5/en/charset.html
To answer the specific questions you asked:
Q: is this a possible way to compare strings in SQL?
A: Yes, in both MySQL and SQL Server
Q: and how does it act?
A: A comparison operator returns a boolean, either TRUE, FALSE or NULL.
Q: a string less than another one comes before in dictionary order? For example, ball is less than water?
A: Yes, because 'b' comes before 'w' in the characteset collation, the expression
'ball' < 'water'
will return TRUE. (This depends on the characterset and on the collation.
Q: and this comparison is case sensitive?
A: Whether a particular comparison is case sensitive or not depends on the database server; by default, both SQL Server and MySQL are case insensitive.
In MySQL it is possible to make string comparisons by specifying a characterset collation that is case sensitive (the characterset name will end in _cs rather than _ci)
Q: For example BALL < water, the upper case character does affect these comparison?
A: By default, in both SQL Server and MySQL, the expression
'BALL' < 'water'
would return TRUE.
