Over the years I've noticed that when writing an SQL statement (at least in the drivers I use most like SQL Server or SQL Developer) it works the same whether you write them in uppercase or lowercase, both for reserved words and table and column names.
For example, based on the following SQL statement:
SELECT
A.ColumnaA, A.ColumnaB, B.ColumnaC
FROM TablaA AS A
INNER JOIN TablaB B
ON A.ColumnaA = B.ColumnaA
WHERE B.ColumnaB = 123
AND A.ColumnaC = 'ABC'
If written in lowercase:
select
a.columnaa, a.columnab, b.columnac
from tablaa as a
inner join tablab b
on a.columnaa = b.columnaa
where b.columnab = 123
and a.columnac = 'abc'
Or if it is written in capital letters:
SELECT
A.COLUMNAA, A.COLUMNAB, B.COLUMNAC
FROM TABLAA AS A
INNER JOIN TABLAB B
ON A.COLUMNAA = B.COLUMNAA
WHERE B.COLUMNAB = 123
AND A.COLUMNAC = 'ABC'
No error is generated, unlike other languages that are totally restrictive both for the use of reserved words and variable names.
However, the ideal is to always write the reserved words in capital letters and leave the names of tables or columns to the consideration of the programmer.
Even making use of Poor Man's T-SQL Formatter plugin for Notepad++ formats reserved words by putting them in uppercase and identifies the query performed ( a wonderful plugin ).
Although the handler does not mark any error in the use of uppercase or lowercase, I mainly have two questions:
- Why doesn't typing an uppercase or lowercase SQL statement throw an error?
- Why is capitalization preferred or recommended?
Why doesn't typing an uppercase or lowercase SQL statement throw an error?
There are programming languages that are case sensitive ( case sensitive or "case sensitive"), and others that are not case sensitive ( case insensitive ).
SQL is a case insensitive language for reserved words. So when the statement is parsed, no error is thrown. For example, it doesn't matter to type
SELECT
orselect
orsELect
, they will all be interpreted the same.It is important to highlight the part " for reserved words " because depending on the database engine used, the operating system in which the database is located, or the format given to the query, yes there could be differences in table or column names. For example:
On some platforms, in MySQL the case of table names must be consistent within the same statement or a failure will occur ( source ). The following statement would show an error:
In MySQL, database and table names are case sensitive on operating systems that are case sensitive. That's because they are associated with directories/files on the ( source ) filesystem .
In Oracle, table and column names can be forced to be case sensitive by enclosing them in double quotes ( Oracle source and SOen ). So these two sentences would be different:
Why is capitalization preferred or recommended?
It is a way to differentiate reserved words from those that are not, facilitating the identification of the names of tables, columns, etc.
That reserved words are valid in both lowercase and uppercase was a decision when developing the sql standard, and all databases that are based on sql implement it, at least all that I know of.
On the other hand, for the names of tables and columns, this is not always the case, although it depends specifically on each database, most sql databases have an option that allows you to choose whether these names are to be treated differently according to the font. For example, for windows systems the default option is that it does not affect whether it is uppercase or lowercase, while in linux systems the default option is that it does make a difference between uppercase and lowercase.
Another aspect that marks the sql standard is that when quotation marks are used to delimit the names of tables and columns, it is indicated that this particular name must be case-sensitive.
For example with:
it is specified that the name of the table must be exactly "TaBlA" it cannot be either "table", nor "TABLE" or any combination that is not like the one specified, although it is possible that in specific databases this characteristic is not present implemented, as an example this option is implemented in PostgreSQL, but not in mySQL.
As for using uppercase for reserved words, it is simply a convention, which makes it easier to identify them when reading an sql statement and to separate the reserved words from the names.
Part of the ANSI99 version of sql that talks about keywords and names.