Reason why oracle is case sensitive?

Question

Is there a reason why Oracle is case sensitive and others like SQL Server, and MySQL are not by default?

I know that there are ways to enable/disable case sensitivity, but it just seems weird that oracle differs from other databases.

I'm also trying to understand reasons for case sensitivity. I can see where "Table" and "TaBlE" can be considered equivalent and not equivalent, but is there an example where case sensitivity would actually make a difference?

I'm somewhat new to databases and am currently taking a class.

Answer 1

By default, Oracle identifiers (table names, column names, etc.) are case-insensitive. You can make them case-sensitive by using quotes around them (eg: SELECT * FROM "My_Table" WHERE "my_field" = 1). SQL keywords (SELECT, WHERE, JOIN, etc.) are always case-insensitive.

On the other hand, string comparisons are case-sensitive (eg: WHERE field='STRING' will only match columns where it's 'STRING') by default. You can make them case-insensitive by setting NLS_COMP and NLS_SORT to the appropriate values (eg: LINGUISTIC and BINARY_CI, respectively).

Note: When inquiring data dictionary views (eg: dba_tables) the names will be in upper-case if you created them without quotes, and the string comparison rules as explained in the second paragraph will apply here.

Some databases (Oracle, IBM DB2, PostgreSQL, etc.) will perform case-sensitive string comparisons by default, others case-insensitive (SQL Server, MySQL, SQLite). This isn't standard by any means, so just be aware of what your db settings are.

Answer 2

Oracle actually treats field and table names in a case-insensitive manner unless you use quotes around identifiers. If you create a table without quotes around the name, for example CREATE MyTable..., the resulting table name will be converted to upper case (i.e. MYTABLE) and will be treated in a case insensitive manner. SELECT * from MYTABLE, SELECT * from MyTable, SELECT * from myTabLe will all match MYTABLE (note the lack of quotes around the table name). Here is a nice article on this issue that discusses this issue in more detail and compares databases.