50

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!
Naftuli Kay
  • 1,621
  • 2
  • 16
  • 23
  • 2
    it's case INsensitive, SQL doesn't care whether you type DELETE or delete – ratchet freak Nov 03 '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 04 '14 at 00:41
  • 3
    I agree. I also hate all-caps. – Tulains Córdova Nov 04 '14 at 00: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 04 '14 at 09: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.... – sheldonhull Jan 25 '16 at 15:07
  • Because Computers HAVE TROUBLE HEARING US –  Nov 08 '17 at 14:56

3 Answers3

42

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.

Robert Harvey
  • 199,517
tom
  • 1,912
  • 2
    Also, SQL was developed by IBM, who, with their mainframe heritage, were very comfortable with using capitalization in this way. – Turophile Nov 04 '14 at 04:26
30

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.

Community
  • 1
enderland
  • 12,131
  • 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 03 '14 at 23:41
  • 2
    @tom, IntelliJ IDEA (Java) highlights SQL in String. It even provides auto complete (CTRL + space). –  Nov 03 '14 at 23:59
  • 1
    @tom from some code I have sitting around http://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. –  Nov 04 '14 at 00:16
  • 8
    According to our very own skeptics.se, the chimp experiment is a lie – Matt Giltaji Nov 04 '14 at 00:30
  • 5
    @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 04 '14 at 00:43
  • @Simon Arsenault cheers. Good to know. – tom Nov 04 '14 at 07:56
  • @MattGiltaji If you have bananas on a pole, you'll lose your bananas :D – tom Nov 04 '14 at 15:22
  • 1
    @MattGiltaji, Experiments have been successful countless of times, except those weren't chimps. Those were humans. – Pacerier Jul 03 '15 at 07:18
  • @DaveSmylie, @ tom, Your IDEs are too old. The market is efficient, time to switch. – Pacerier Jul 03 '15 at 10:23
  • Totally makes sense if you're using a text based language that can/does get used outside of an IDE. I do a lot of oracle work mostly from sqlplus - no code highlighting in there, and suspect there never will be =) Personally I don't mind either syntax, but tend to use capitals myself for just this reason. – Dave Smylie Jul 04 '15 at 22:42
  • @Pacerier: You miss the point - sqlplus is NOT an ide - it's a minimalist/low overhead text interface into a database that is almost guaranteed to be present on any oracle installation.. I'm currently upgrading a system in Guam bouncing via a server in PNG (I'm in NZ). Any sort of IDE would be refreshing the screen once a minute - in console/sqlplus I have pretty much zero lag.... – Dave Smylie Jul 04 '15 at 22:43
  • Upvoted purely for a chimp allegory. Although I think the story needs to have a point where the detector and hoses are removed, so not only do the chimps not know why they beat up newcomers for daring to climb the stepladder, but in fact there is no good reason any more. – Peter Bagnall Jul 13 '21 at 09:29
-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.

ChrisInEdmonton
  • 165
  • 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 s, any contained in a candidate shall be effectively treated as the corresponding ."* – ypercubeᵀᴹ Nov 04 '14 at 01: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 04 '14 at 01: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 03 '15 at 06: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 03 '15 at 17:06