144

Possible Duplicate:
Is there a good reason to use upper case for T-SQL keywords?

I personally find a string of lowercase characters to be more readable than a string of uppercase characters. Is some old/popular flavor of SQL case-sensitive or something?

For reference:

select
    this.Column1,
    case when this.Column2 is null then 0 else this.Column2 end
from dbo.SomeTable this
    inner join dbo.AnotherTable another on this.id = another.id
where
    this.Price > 100

vs.

SELECT
    this.Column1,
    CASE WHEN this.Column2 IS NULL THEN 0 ELSE this.Column2 END
FROM dbo.SomeTable this
    INNER JOIN dbo.AnotherTable another ON this.id = another.id
WHERE
    this.Price > 100

The former just seems so much more readable to me, but I see the latter way more often.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Samantha Branham
  • 7,350
  • 2
  • 32
  • 44
  • 3
    I would argue making your keywords standout is not important. In SQL they come in a predictable order and tend to separate lists of things. See my answer below. – WW. Mar 04 '09 at 02:58
  • 13
    I would argue that it is important. It's the same reason you have color coding in your text editor: having a visual distinction increases readability. If I'm not worrying about what types I'm dealing with, I subconsciously ignore blue text, for example. – Samantha Branham Mar 04 '09 at 05:57
  • @Mitch: No, we just don't search before answering a question we're never seen before. Do you? The only solution is for regular visitors to warn everyone with a "possible duplicate" early comment, so people know to move on. – Stefan Monov Sep 23 '10 at 08:06
  • 1
    @Stefan Monov: yes I do. Given that its easy to do, there's very little raeson not to. – Mitch Wheat Sep 23 '10 at 10:48
  • it's a COBOL thing i think - people who capitalise generally are over 40 and have only programmed COBOL and SQL. – Jon Black Dec 03 '10 at 04:33
  • 5
    As a side note, I find typing uppercase keywords grueling over time. I type hundreds of queries a week and lowercase is much easier on the pinkies. For documentation and SQL that other developers may have to read and modify, capitalizing makes a lot of sense for readability. – wsams Jun 02 '16 at 15:32

16 Answers16

206

I agree with you - to me, uppercase is just SHOUTING.

I let my IDE handle making keywords stand out, via syntax highlighting.

I don't know of a historical reason for it, but by now it's just a subjective preference.

Edit to further make clear my reasoning:

Would you uppercase your keywords in any other modern language? Made up example:

USING (EditForm form = NEW EditForm()) {
    IF (form.ShowDialog() == DialogResult.OK) {
       IF ( form.EditedThing == null ) {
          THROW NEW Exception("No thing!");
       }
       RETURN form.EditedThing;
    } ELSE {
       RETURN null;
    }
}

Ugh!

Anyway, it's pretty clear from the votes which style is more popular, but I think we all agree that it's just a personal preference.

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
  • 13
    it's a COBOL thing i think - people who capitalise generally are over 40 and have only programmed COBOL and SQL. – Jon Black Dec 03 '10 at 04:34
  • 5
    This is a pretty good example of why not to do it. Looking at this does make me cringe although I do typically uppercase my SQL keywords. One thing different between SQL and C# is that C# has a pretty well defined structure to it with the braces and standard indenting while SQL doesn't. It's possible the uppercasing is lending some form of visual information in the absence of this structure. – User Jul 29 '11 at 23:53
  • 10
    Different languages lend themselves to different styling conventions. – Isabelle Wedin Oct 26 '11 at 19:48
  • 12
    This is a good example of why SQL *does* need uppercase keywords: because it uses those keywords for most of the syntactic cues. Try replacing just about all that punctuation with words, make it all lowercase, and then see how difficult the syntax is to parse with the eye. – bignose Aug 14 '12 at 01:15
  • 2
    You just convinced me to switch to UPPERCASE for SQL. That code your wrote looks very nice-- I was surprised that you said "Ugh!" Being a former C-programmer, this brings me back to the power days of MACROS. – David Betz Jul 22 '13 at 18:38
  • (1) The difference is that SQL has hundreds of keywords with minimal syntactic hints about what is a keyword. No semicolons, just a long string of words that can be parsed to mean something. (2) Ironically, you demonstrate that all caps is used in modern programming languages `DialogResult.OK`. – Paul Draper Aug 24 '21 at 14:50
131

I think the latter is more readable. You can easily separate the keywords from table and column names, etc.

Trent
  • 13,249
  • 4
  • 39
  • 36
  • 7
    Perhaps it's just habit, but I find the second version much more understandable. – Daniel Von Fange Mar 03 '09 at 21:19
  • 207
    I"M NOT SURE IF THAT IS MORE READABLE :) – Learning Mar 04 '09 at 05:48
  • 3
    I think it's interesting that SO doesn't do the same code highlighting when it's in caps. – William Holroyd Mar 04 '09 at 06:05
  • 7
    @Learning `THE fantastic.words WHICH ARE yours STAND OUT as_compared_to THE few AND often_patterned/repeated SQL WORDS` In other words, the interest of the UPPERCASE convention is that SQL immutable keywords are easily identified, and look separate/different from your own identifiers and immediate values. This of course matters a bit less, nowadays with the omnipresent syntax hi-lighting in SQL IDEs / editors. This said, while this convention is a good thing for DML/DDL such as SELECT/INSERT queries and such, it can make for heavy look of Procedural extensions to SQL as in stored procs. – mjv Dec 09 '09 at 04:34
  • 85
    BILLY MAYS here, bringing you STUCTURED QUERY LANGUAGE! – Shea Daniels Feb 25 '10 at 20:52
  • 3
    it's a COBOL thing i think - people who capitalise generally are over 40 and have only programmed COBOL and SQL. – Jon Black Dec 03 '10 at 04:33
  • I use textmate and all I need to do to trigger the sql syntax highlighting is have the first keyword in caps, the rest I just type as usual. SELECT * from table where col1 is not null; So that's my compromise. – Chris Hawkins Mar 26 '14 at 14:44
  • 1
    @Trent It depends. I am currently working in a project that uses uppercase for all tables, columns, fields etc. so that an SQL statement would be all uppercase, e.g. `SELECT ID,NAME FROM TABLEABC WHERE ID>10`. So in this case using lowercase keywords is definitely the way to make SQL statements more readable. ;) – Andreas Nov 25 '15 at 08:57
  • Well, today's editors will give you a visual notion of what is acutally SQL command and what is the table's name, field and etc. If you're running on terminal, chances are you won't have any colour, so capitalizing is an alternative to make sql statements more readable. – Luis Milanese Jul 11 '16 at 19:44
  • Personally i find it a lot easier to distinguish what is going on. Also the MySQL documentation is written using capitalisation. They know what they're doing ;) – TPHughes Jan 30 '20 at 14:43
  • I just look at the number of key presses I have left in my life and stick with lower case. Maybe the convention started before colorization. You can get similar readability effects through layout/whitespace. And using a case sensitive db like postgres reinforces my lack of capitalization due to having to quote to retain capitals, whereas the TSQL standard is to use PascalCase names. I don't use an ALL_CAPS_AND_UNDERSCORES for my constants, either-- they seem to be relics from the past imo. We have better tooling now, so that's something to consider. – ps2goat Jul 29 '21 at 20:23
  • WELL THAT IS CERTAINLY AN OPINION YOU HAVE PROVIDED. I MUST TAKE THIS INTO ACCOUNT WHEN TRYING TO EXAMINE MY VERY OWN CODING STYLES BUT WHAT YOU HAVE GIVEN ME WILL CERTAINLY GIVE ME A LOT TO THINK ABOUT SO I THANK YOU FOR IT, ALSO, IF YOU ARE AVAILBLE FOR DINNER, I CAN COME AROUND OVER AT ABOUT 8:30 BUT BEWARE THAT I HAVE A COUPLE OF ERRANDS TO RUN SO MAYBE WE SHOULD SETTLE FOR 8:32, SO I CAN TAKE CARE OF BUSINESS. ADIOS, DET. – Det Sep 19 '21 at 11:39
87

One thing I'll add to this which I haven't seen anyone bring up yet:

If you're using ad hoc SQL from within a programming language you'll have a lot of SQL inside strings. For example:

insertStatement = "INSERT INTO Customers (FirstName, LastName) VALUES ('Jane','Smith')"

In this case syntax coloring probably won't work so the uppercasing could be helping readability.

User
  • 62,498
  • 72
  • 186
  • 247
  • 4
    FWIW IntelliJ (probably & friends) has language injection and can syntax color (etc.) SQL strings in code (as well as other embedded languages -- seems they take the concept of html/css/js in one file and generalize it) – nafg Nov 29 '15 at 05:16
  • 9
    This IMHO is the only valid reason (besides personal preference) to use all caps keywords. – Amani Kilumanga Jan 29 '16 at 01:47
  • 2
    Again, you have 2 options here: 1) small ad hoc query without proper indentation (which, IMO, is wrong) and it still doesn't need any capitalization; 2) long multiline query pre-written in your IDE-of-choice with syntax highlighting. – pkuderov Apr 28 '16 at 11:14
55

From Joe Celko's "SQL Programming Style" (ISBN 978-0120887972):

Rule:

Uppercase the Reserved Words.

Rationale:

Uppercase words are seen as a unit, rather than being read as a series of syllables or letters. The eye is drawn to them, and they act to announce a statement or clause. That is why headlines and warning signs work.

Typographers use the term bouma for the shape of a word. The term appears in paul Saenger's book (1975). Imagine each letter on a rectangular card that just fits it, so that you see the ascenders, descenders, and baseline letters as various "Lego blocks" that are snapped together to make a word.

The bouma of an uppercase word is always a simple, dense rectangle, and it is easy to pick out of a field of lowercase words.

What I find compelling is that this is the only book about SQL heuristics, written by a well-known author of SQL works. So is this the absolute truth? Who knows. It sounds reasonable enough and I can at least point out the rule to a team member and tell them to follow it (and if they want to blame anyone I give them Celko's email address :)

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • 2
    yeah but you could apply the same thinking to programming languages, imagine a js function like this: `LET echoMessage = FUNCTION(msg){ ALERT(msg); }` – santiago arizti Dec 05 '17 at 18:03
  • @santiagoarizti yeah but is your suggested formatting based on the only js heuristics book on the market and is written by a well known author in the js world? – onedaywhen Dec 06 '17 at 10:48
  • 6
    I am going to write all my JS in uppercase from now on and make a Babel plugin to transform it. Thank you. – Danyal Aytekin Jul 02 '18 at 16:02
  • 1
    Well, for people who learn English as the second language, all uppercase words are just too different to be recognized. We need to transformed character by character into lowercase before we can recognize the word. – Louis Yang Jul 06 '18 at 07:02
  • @LouisYang I don't know where you got that statement from. English is a second language of mine and I find it very easy to differentiate keywords from variables when capitalization is used and I have no trouble reading it. – Fanatique May 11 '21 at 11:45
  • @santiagoarizti You don't write js code in terminal though, do you. I agree that no capitalization is necessary when you're writing SQL *only* in an IDE. However, when you also have to write in a terminal - then capitalization becomes the only way to make keywords differ from the rest. And for me, being able to easily spot keywords is an absolute necessity. Doesn't matter the way it is done - color, capitalization, or something else. – Fanatique May 11 '21 at 11:50
  • @Fanatique Probably because my native language is Chinese. I recognize a word by the shape rather than the pronunciation. ALL CAPITAL LETTERS ARE JUST LOOK VERY DIFFERENT TO ME. It almost double my reading time on all cap letters. – Louis Yang May 12 '21 at 18:26
  • @Fanatique I do write in the terminal actually, I use mysql cli a lot, I just don't consider that formal code because it will not get tracked and will not bee seen by anybody but myself. Also, I deal with that problem the same as when I am using node or php in the terminal: I am familiar enough with the language so that I recognize all special keywords just fine, and those languages don't recommend caps do they? – santiago arizti May 12 '21 at 19:49
27

Code has punctuation which SQL statements lack. There are dots and parentheses and semicolons to help you keep things separate. Code also has lines. Despite the fact that you can write a SQL statement on multiple physical lines, it is a single statement, a single "line of code."

IF I were to write English text without any of the normal punctuation IT might be easier if I uppercased the start of new clauses THAT way it'd be easier to tell where one ended and the next began OTHERWISE a block of text this long would probably be very difficult to read NOT that I'd suggest it's easy to read now BUT at least you can follow it I think

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Instance Hunter
  • 7,837
  • 5
  • 44
  • 56
  • 3
    Sql statement is not a single line of code. It is more like a procedure in a procedural language, not a statement. – hipe Oct 26 '15 at 10:05
  • 2
    how much punctuation do ruby, haskell, and python have? – nafg Nov 29 '15 at 05:17
  • 1
    That English is much harder to read than it would be if it didn't stress those words. I have no problem adding punctuation in my head without thought, so the example might not be a great one. :) – person27 Jan 03 '18 at 19:17
  • I can't speak to Ruby and Haskell, but Python uses semantically significant indentation where many other languages use punctuation. SQL has neither punctuation nor significant indentation, so capitalization is what's left. As an aside, in my experience, Python is also harder for an IDE to colorize effectively precisely due to its free-form nature. – Clement Cherlin Mar 07 '22 at 20:09
24

Mostly it's tradition. We like to keep keywords and our namespace names separate for readability, and since in many DBMSes table and column names are case sensitive, we can't upper case them, so we upper case the keywords.

Paul Tomblin
  • 179,021
  • 58
  • 319
  • 408
  • 3
    *MANY* DBMS use case-sensitive table & columns names? I've never run into any (however 90% of my experience is MSSQL & Oracle) – James Curran Mar 03 '09 at 21:31
  • 2
    A MSSQL server or database *can* be set to a case-sensitive collation. And yes, its very annoying. But required for some applications. – BradC Mar 03 '09 at 21:35
  • 1
    Sybase servers have case sensitive names by default. – Learning Mar 04 '09 at 05:33
  • 1
    So does MySQL, and for good and for bad (mostly bad:-), many developers first exposure to SQL is through MySQL. – Paul Tomblin Mar 04 '09 at 13:41
  • @JamesCurran Just be glad you don't have to deal with Postgres... If you don't double-quote your identifiers, it lowercases them for you, which sounds semi-sane until you realise you can bypass this using quotes and that most ORMs tend to use this feature, meaning you get left with garvage like this... `SELECT * FROM "MyTable" WHERE "MyField" = 'something';` – Basic Jan 05 '17 at 17:04
  • @JamesCurran postgres... i mean, PostGres – Ringo Jan 03 '21 at 20:19
20

I prefer lower case keywords. SQL Server Management Studio color codes the keywords, so there is no problem distinguishing them from the identifiers.

And upper case keywords feels so... well... BASIC... ;)

-"BASIC, COBOL and FORTRAN called from the eighties, and they wanted their UPPERCASE KEYWORDS back." ;)

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
8

What's worse it that as the majority of developers at my office believe in capitals for SQL keyword, so I have had to change to uppercase. Majority rules.

I believe lowercase is easier to read and that given that SQL keywords are highlighted in blue anyway.

In the glory days, keywords were in capitals because we were developing on green screens!

The question is: if we don't write C# keywords in uppercase then why do I have to write SQL keywords in uppercase?

Like someone else has said - capitals are SHOUTING!

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Steve
  • 89
  • 1
  • 1
7

I like to use upper case on SQL keywords. I think my mind skips over them as they are really blocky and concentrates on what's important. The blocky words split up the important bits when you layout like this:

SELECT
  s.name,
  m.eyes,
  m.foo
FROM
  muppets m,
  muppet_shows ms,
  shows s
WHERE
  m.name = 'Gonzo' AND
  m.muppetId = ms.muppetId AND
  ms.showId = s.showId

(The lack of ANSI joins is an issue for another question.)

There is a psychology study that shows lowercase was quicker to read than uppercase due to the outlines of the words being more distinctive. However, this effect can disappear about with lots of practice reading uppercase.

WW.
  • 23,793
  • 13
  • 94
  • 121
3

Back in the 1980s, I used to capitalize database names, and leave SQL keywords in lower case. Most writers did the opposite, capitalizing the SQL keywords. Eventually, I started going along with the crowd.

Just in passing, I'll mention that, in most published code snippets in C, C++, or Java the language keywords are always in lower case, and upper case keywords may not even be recognized as such by some parsers. I don't see a good reason for using the opposite convention in SQL that you use in the programming language, even when the SQL is embedded in source code.

And I'm not defending the use of all caps for database names. It actually looks a little like "shouting". And there are better conventions, like using a few upper case letters in database names. (By "database names" I mean the names of schemas, schema objects like tables, and maybe a few other things.) Just because I did it in the 80s doesn't mean I have to defend it today.

Finally, "De gustibus non disputandum est".

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
2

It's just a matter of readability and helps you quickly distinguish SQL keywords.

Btw, that question was already answered: Is SQL syntax case sensitive?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
rogeriopvl
  • 51,659
  • 8
  • 55
  • 58
2

I prefer using upper case as well for keywords in SQL.

Yes, lower case is more readable, but for me having to take an extra second to scan through the query will do you good most of the time. Once it's done and tested you should rarely ever see it again anyway (DAL, stored procedure or whatever will hide it from you).

If you are reading it for the first time, capitalized WHERE AND JOIN will jump right at you, as they should.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
jfrobishow
  • 2,897
  • 2
  • 27
  • 42
2

It’s just a question of readability. Using UPPERCASE for the SQL keywords helps make the script more understandable.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
1

I capitalize SQL to make it more "contrasty" to the host language (mostly C# these days).

It's just a matter of preference and/or tradition really...

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
0

Some SQL developers here like to lay it out like this:

SELECT s.name, m.eyes, m.foo
FROM muppets m, muppet_shows ms, shows s 
WHERE m.name = 'Gonzo' AND m.muppetId = ms.muppetId AND ms.showId = s.showId

They claim this is easier to read unlike your one field per line approach which I use myself.

skink
  • 5,133
  • 6
  • 37
  • 58
Steve
  • 89
  • 1
  • 1
0

Apropos of nothing perhaps, but I prefer typesetting SQL keywords in small caps. That way they look capitalized to most readers, but they aren't the same as the ugly ALL CAPS style.

A further advantage is that I can leave the code as is and print it in the traditional style. (I use the listings package in LaTeX for pretty-printing code.)

Jon 'links in bio' Ericson
  • 20,880
  • 12
  • 98
  • 148