52

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.

Möoz
  • 847
  • 2
  • 14
  • 31
Steve
  • 1,145
  • 1
  • 11
  • 25
  • 7
    Are you talking about Oracle keywords, table names, column names, etc? In my experience these are *not* case-sensitive. What does happen that's a bit weird is Oracle outputs these identifiers in all upper case, but you can enter them with any case you want. – Mike Sokolov Sep 15 '11 at 02:29
  • 1
    Which case sensitivity thing are you particularly referring wrt others ? – V4Vendetta Sep 15 '11 at 04:55
  • 8
    `"it just seems weird that oracle differs from other databases."` Lol, welcome to Oracle! – Möoz Dec 02 '16 at 02:12
  • The question doesn't make sense. Oracle isn't case-sensitive, unless you mean `where name = 'Smith'` vs `where name = 'SMITH'`, but surely that is standard. Plus it's an opinion-based question and so out of scope for SO. Voting to close, belatedly. – William Robertson Dec 23 '21 at 18:01

5 Answers5

81

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.

NullUserException
  • 83,810
  • 28
  • 209
  • 234
  • 1
    In addition, database table and column names are often case-sensitive when the Operating System on which they are installed is case sensitive. For instance, a MySQL table named "Foo" on a Linux install could not be referenced as "SELECT * FROM foo", it would have to be "SELECT * from Foo". On Windows, either statement would be valid. – Adrian J. Moreno Sep 15 '11 at 18:04
  • 1
    MySQL OS case-sensitivity behavior depends on the particular engine being used IIRC. MySQL is also much more the exception than the rule since most databases are not dependent of OS file system casesensitivity. – David Taylor Sep 23 '11 at 03:16
10

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.

David Taylor
  • 2,021
  • 21
  • 25
4

Keep in mind too for SQL Server the case sensitivity is based on the collation. The default collation is case insensitive - but this could be changed to be case sensitive. A similar example is why do the default Oracle databases use a Western European character set when UTF is required for global applications that use non ASCII characters? I think it's just a vendor preference.

tsells
  • 2,751
  • 1
  • 18
  • 20
3

If I had to guess, I'd say for historical/backwards-compatibility reasons.
Oracle first came out in 1977, and it was likely computationally expensive with the technology at the time to do the extra work for case-insensitive searches, so they just opted for exact matches.

Gerrat
  • 28,863
  • 9
  • 73
  • 101
0

For some applications case-sensitivity is important and for others it isn't. Whichever DBMS you use, business requirements should determine whether you need case-senitivity or not. I wouldn't worry too much about the "default".

nvogel
  • 24,981
  • 1
  • 44
  • 82