I have been using new databases such as mysql and mariaDB for the last few years.
For a project I am now using oracle after many years and I was surprised to see that Oracle order by is case sensitive... UpperCase characters go above LowerCase characters for example in ASC.
Is this behavior following the SQL specifications? Do the general SQL language specs outline what the behavior should be or is it up to each vender?
I'm asking because many years of using mysql/mariaDB made me consider order by to be case insensitive.
I see for example that in the documentation for mysql:
On character type columns, sorting—like all other comparison operations—is normally performed in a case-insensitive fashion. This means that the order is undefined for columns that are identical except for their case. You can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY col_name.
From: http://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html
From Oracle Database SQL Language Reference, 12c Release 1 (12.1):
When character values are compared linguistically for the ORDER BY clause, they are first transformed to collation keys and then compared like RAW values. The collation keys are generated either explicitly as specified in NLSSORT or implicitly using the same method that NLSSORT uses. Both explicitly and implicitly generated collation keys are subject to the same restrictions that are described in "NLSSORT" on page 7-207. As a result of these restrictions, two values may compare as linguistically equal if they do not differ in the prefix that was used to produce the collation key, even if they differ in the rest of the value.
Hope, this will help you!
Have a nice day!