Why are unquoted identifiers upper-cased per SQL-92?

Question

Section 5.6 of the SQL-92 standard contains rules 10...13 per which unquoted identifiers should be upper-cased, so foo becomes FOO but "foo" remains foo.

These rules are respected by Oracle, IBM DB2, Snowflake, and ksqlDB but not by Postgres, MySQL or SQLite, for example.

The question is, why? In my understanding, the optional quoting of identifiers in a language with a lot of keywords makes sense. Consistent case sensitivity or insensitivity of identifiers would also make sense. But making it dependent on the identifier being quoted doesn't look rational.

What am I missing?

Answer 1

But making it dependent on the identifier being quoted doesn't look rational

Case-sensitive identifiers suck. Imagine the chaos of having tables named Foo, FOO, and foo, and all the frustrating erros of users who write SQL queries and get the case of objects wrong. But some older RDBMS system were inherently case-sensitive (Oracle I'm looking at you).

So transforming identifiers to all caps is an old-fashioned workaround to not having the ability to support a case-insensitive catalog.

So create table Foo ... actually creates a table named "FOO" and in a SQL Query select * from foo the object name is uppercased before searching for it in the catalog.

But having support for mixed case identifiers is an important feature. So if regular identifiers are implicitly converted to all caps, it makes some sense to allow mixed case identifiers if explicitly delimited with ".

For instance SQL Server has mixed case identifiers, and an application might rely on reading the mixed case identifiers from the catalog. This is possible in Oracle by creating the table with mixed case quoted identifiers.

Also this describes the actual behavior of Oracle, which was one of the only RDBMS systems around back then. So it also may be that the the Oracle design is being codified by the SQL standard.

Answer 2

Originally, the quotes were not used. This meant that identifiers could not use reserved names such as index and table. And case of identifiers was irrelevant. However, the convention was to use uppercase identifiers not just in the database but also in many programming languages.

Later the quotes were allowed, making possible the ability to use mixed case identifiers as well as reserved names. On some databases, you can choose which option you want to use.

The sql-92 standard has been a little ambiguous allowing different languages to behave a little differently. There are quirks within a language such as Firebird. Older ones such as Oracle have had to respect legacy usage.

As an example in Firebird/Interbase, say one is using dialect 3 (telling the database that quoted identifiers will be used). If an identifier is left unquoted it is treated as dialect-1 identifier. Theoretically, it should be upper case, but the case is actually irrelevant.

Some languages may insist on upper case identifiers being upper case. But all this is because of historical/legacy support and/or newer decisions they have had to make in their design. It would appear that Some have decided to make it easier for the user, whereas others are adhering strictly to the standard.

Standards

It would be useful to remember that the languages were not originally designed to comply with a standard, let alone sql-92. They were adapted to comply with the standard. So, implementations vary a little bit.

Answer 3

Why are unquoted identifiers upper-cased per SQL-92?

Likely for historical reasons: when the SQL language was developed, many computer systems were only capable of accepting, processing, and outputting uppercase letters. Later, when terminals and output devices began supporting larger character sets that included lowercase letters, it was only natural to fold them by default to the upper case for backward compatibility.