41

This question already has an answer here:

On MySQL at least, the following two queries are functionally identical:

select * from users limit 0, 1000;
SELECT * FROM users LIMIT 0, 1000;

However, most example sites and most developers I've worked with use the latter, while I prefer the former.

Is there a reason to write SQL statements in all-caps? I find it to be more of a strain to hold the Shift key or to engage Caps-Lock for all SQL language statements.

Plus, I can't help but read statements as someone screaming:

DELETE FROM users WHERE id = 12345;
# OBLITERATE EVERYTHING FROM USERS WHERE THE ID IS 12345!!!!!!!!!!!!! NAO!

marked as duplicate by user40980, user22815, GrandmasterB, GlenH7, Steven A. Lowe Nov 4 '14 at 5:06

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 2
    it's case INsensitive, SQL doesn't care whether you type DELETE or delete – ratchet freak Nov 3 '14 at 23:41
  • 2
    Personally, I use all-caps when writing documentation or examples for other SQL devs or when my SQL queries are committed to a codebase, but I usually write all my direct queries to a database in lowercase because of the reasons you bring up. – Kevin Nov 4 '14 at 0:41
  • 3
    I agree. I also hate all-caps. – Tulains Córdova Nov 4 '14 at 0:43
  • This way it's easier to tell it from actual normal programming languages. Maybe it will stop people from using it for implementing business logic. – Den Nov 4 '14 at 9:11
  • your intelliense/code reformatting tool should do all the capitalizing or stripping caps, in my opinion. I personally set all to lower, hate upper case, more work for no gain. Personal preference for me. I use SQL complete (a competitor to Red Gate sql prompt) and i set it to use lower case, EXCEPT for preserving variable and identifier case defined in schema. I do this because unfortunately my new place uses camel case :-( I_do_miss_those_underscores.... – SheldonH Jan 25 '16 at 15:07
38

It's using case as a form of syntax highlighting. It makes the logic distinct from the table names and field names.

It's a form that predates new-fangled things like having more than one colour on a screen. We used to do it with UCSD pascal too.

  • 2
    Also, SQL was developed by IBM, who, with their mainframe heritage, were very comfortable with using capitalization in this way. – Turophile Nov 4 '14 at 4:26
25

Is there a reason to write SQL statements in all-caps?

  1. Shows keywords more clearly, even if your IDE is fancy and highlights/colors.
  2. Some editors/languages still might not have syntax highlighting.
  3. People did it this way on because color highlighting didn't exist

Related to the last, an example to make things clear:

Take five chimpanzees. Put them in a big cage. Suspend some bananas from the roof of the cage. Provide the chimpanzees with a stepladder. BUT also add a proximity detector to the bananas, so that when a chimp goes near the banana, water hoses are triggered and the whole cage is thoroughly soaked.

Soon, the chimps learn that the bananas and the stepladder are best ignored.

Now, remove one chimp, and replace it with a fresh one. That chimp knows nothing of the hoses. He sees the banana, notices the stepladder, and because he is a smart primate, he envisions himself stepping on the stepladder to reach the bananas. He then deftly grabs the stepladder... and the four other chimps spring on him and beat him squarely. He soon learns to ignore the stepladder.

Then, remove another chimp and replace it with a fresh one. The scenario occurs again; when he grabs the stepladder, he gets mauled by the four other chimps -- yes, including the previous "fresh" chimp. He has integrated the notion of "thou shallt not touch the stepladder".

Iterate. After some operations, you have five chimps who are ready to punch any chimp who would dare touch the stepladder -- and none of them knows why.

Taken from this answer as it is directly applicable.

After a while, conventions become the way people do things even if/when they no longer make sense.

  • 3
    It still makes total sense if you're embedding SQL within a programming language as Strings. I've not met an IDE yet that will syntax highlight them. – tom Nov 3 '14 at 23:41
  • 1
    @tom, IntelliJ IDEA (Java) highlights SQL in String. It even provides auto complete (CTRL + space). – user92912 Nov 3 '14 at 23:59
  • 1
    @tom from some code I have sitting around i.stack.imgur.com/IsNcP.png - note the syntax highlighting of the SQL. Furthermore, if there was a typo in the column names or table to a connected database, it would note it. That said, I still type SQL parts in all caps myself because not everyone uses IntelliJ. – user40980 Nov 4 '14 at 0:16
  • 7
    According to our very own skeptics.se, the chimp experiment is a lie – Matt Giltaji Nov 4 '14 at 0:30
  • 4
    @MattGiltaji whether or not the story is officially reproducible, the effects of "it once was this way so we still do it this way" are felt constantly for archaic processes - regardless of whether they are needed still. The example is still useful in answering the asker's question. – enderland Nov 4 '14 at 0:43
-2

The SQL standard from 1992 required upper-case for keywords. Specifically, the list of reserved words is specified entirely in upper-case. See section 5.2 of the standard, ISO/IEC 9075:1992.

Now, almost all (possibly all) implementations didn't care. And, if I remember correctly, there have been subsequent editions of the SQL standard that did not require keywords be specified in upper-case.

  • 9
    This is not true. The link you provide states very clearly (sic), in the very 5.2 section: "15)For the purposes of identifying <key word>s, any <simple Latin lower case letter> contained in a candidate <key word> shall be effectively treated as the corresponding <simple Latin upper case letter>." – ypercubeᵀᴹ Nov 4 '14 at 1:06
  • 1
    I don't have a reference for it, but I am fairly certain that the ANSI 92 SQL standard had also removed the requirement for upper case keywords. It may have been in the ANSI 89 SQL standard, but that was quite some time ago... – Adam Zuckerman Nov 4 '14 at 1:18
  • @AdamZuckerman, When you talk about standards, a link/quote is mandatory. Something like "I don't have a reference for it but" just don't cut it. – Pacerier Jul 3 '15 at 6:49
  • 1
    @Pacerier: that was over 20 years ago. I am not certain that documentation still exists in an unmodified form. Hence the reason for a comment, not an answer. – Adam Zuckerman Jul 3 '15 at 17:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.