Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

This question already has an answer here:

Is there are a widely accepted standard for formatting SQL queries? I've never been given any guidance as to the "typical" or "standard" way of doing it. I've seen three styles in the wild, and prefer the second:

All caps:

SELECT GROUP_NAME FROM GROUP_TABLE WHERE GROUP_CODE = 'GRP37X';

Reserved words in lower case:

select GROUP_NAME from GROUP_TABLE where GROUP_CODE = 'GRP37X';

Reserved words in caps, variables in lower case:

SELECT group_name FROM group_table WHERE group_code = 'GRP37X';

Is there a consensus on SQL syntax formatting?

share|improve this question
add comment

migrated from stackoverflow.com May 7 '11 at 10:53

This question came from our site for professional and enthusiast programmers.

marked as duplicate by gnat, MichaelT, Dynamic, GlenH7, Martijn Pieters Jun 17 '13 at 0:09

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.

6 Answers

I like using the SQL keywords

SELECT, FROM, WHERE, etc in UPPERCASE

As far as table names and column names I write them as they exist in the table/column

share|improve this answer
    
This is how try I do too –  Adrian May 6 '11 at 16:22
    
Me two. When I create tables/rows these are usually all lowercase to stand out from the keywords. –  Loki Astari May 7 '11 at 11:06
add comment

No, there is no consensus on SQL formatting.

The most common things people agree on is to distinguish between columns/tables and functionality. IE: SELECT, GROUP BY, HAVING... are in uppercase, while column references are lowercase to make it more obvious what is what.

I like to line delimit my clauses - you'll often see me re-write queries on SO as:

SELECT t.group_name 
  FROM GROUP_TABLE t
 WHERE t.group_code = 'GRP37X'

Some put the comma at the start of a column reference rather than the end - makes commenting out specific columns easier:

SELECT t.group_name 
     , t.column
  FROM GROUP_TABLE t
 WHERE t.group_code = 'GRP37X'

vs:

SELECT t.group_name, 
       t.column
  FROM GROUP_TABLE t
 WHERE t.group_code = 'GRP37X'
share|improve this answer
1  
I always see the reference to it making commenting columns easier. In actuality it's a wash. All middle columns are the same. The only difference is that it takes removing a comma to comment out the first column instead of the last. –  Tom H. May 6 '11 at 16:27
    
@Tom H: I agree - it's seldom I run into cases that require such commenting. –  OMG Ponies May 6 '11 at 16:28
add comment

Typically, the reserved words are in all caps while your table and column references match whatever is in the database. For example, if you save your tables in Pascal case then you put them into your T-SQL in Pascal case.

I say typically because it does depend on your business rules for such things. As you say, people do it in all different ways. SQL does not have a preference so it is up to the powers that be to specify how casing should be handled.

I did find a good link to a site that provides what they believe should be the business rules for everything in SQL server, not just the syntax casing: http://www.nyx.net/~bwunder/dbChangeControl/standard.htm

I would disagree with their casing, but I deeply respect the fact that they have a set standard that they stick to. That is more important than what the standard is.

share|improve this answer
    
Vendor-specific SQL does have a preference - Oracle assumes uppercase (or is it lowercase?) for table and column names unless you enclose in double quotes (which adds support for non ASCII). –  OMG Ponies May 6 '11 at 16:31
add comment

I don't know about a consensus, but I personally use #3 and for the most part I see 1 and 3 the most (excluding Microsoft's own code).

I don't like all caps because it makes reading more tedious (even if only subconsciously.

As Biggs says, the case of objects typically follows the person's own naming standards. I use all lower variable and column names with mixed-case table names and underscores between words. I find that to be the easiest to read quickly, and reading and understanding code is more important to me than the minimal amount of time spent actually typing it. Anyway, since that's how I name objects, that's how they appear in my code.

Of course, since I'm a consultant, when I go into a new client I follow whatever naming standards they have in place, sharing my own views on it if they ask.

share|improve this answer
2  
Depends on the collation of the database as to whether TSQL is case sensitive for object and variable names. –  Martin May 6 '11 at 16:28
    
You are of course correct Martin... making the edit now –  Tom H. May 6 '11 at 16:30
add comment

If you need to understand a query, say, to fix a bug, you need to be able to look at it and quickly answer some questions:

  • how many columns in the result set?
  • what are their names?
  • what tables are involved in the query?
  • what's the join criteria?

So, to enhance understanding speed of comprehension, I like to make my SQL as tabular as possible, especially WRT to the from clause and the join criteria. In the select list, I like to use the column-name = expression syntax as it makes things much more readable. I also omit keywords that are optional in the SQL grammar. There's no point in saying left outer join' whenleft joinwill do: there is no such thing as aleft inner join`.

This isn't very important for trivial queries: if your query is something on the lines of

select * from foo where foo_date between @lowBound and @hiBound

it doesn't matter much. But, the bigger and more complicated the queries get, the more important it all becomes. If your query runs to 400 lines long and involves 18 tables with nested subqueries, very complicated join criteria and assorted other ugliness, having well-formatted SQL is very important to your ability to understand the code. This is true even if you're the author of the code in question. When it comes to the ability to create "write once" code, Perl has nothing on SQL.

A lot of years of doing maintenance work on existing, sloppy code has led me to a form that looks something like this:

select column_name         = <some-expression>    ,
       another_column_name = <another-expression> ,
       ...
from      dbo.some_table        t1
left join dbo.another_table     t2 on t2.some_table_id = t1.id
join      dbo.yet_another_table t3 on t3.id            = t2.another_table_id
                                  and exists ( select ...
                                               from ...
                                               where <correlation-criteria>
                                               and <filtering-criteria
                                             )
join ( select ...
       from ...
       where ...
     ) t4 on t4.id = t3.widget_id
where t1.xxx = t4.yyy
  and t2.zzz = ( select count(*)
                 from ...
                 where ...
               )

Gerald Weinberg pointed out back in the early 1970s in his seminal work, The Pyschology of Computer Programming, that the most important language for a computer programmer to know is English, not [insert-the-programming-language-of-choice-here]: the writing and maintenance of computer programs is a social task (something one does with people: the computer is merely a tool). As far as actual source code goes, the compiler doesn't care what it looks like, so long as it parses correctly. However, the people who need to work with that source code do care: they need to be able to grok it.

The human mind likes order: whitespace and tabular column alignment are your friends.

share|improve this answer
add comment

I would recommend either sticking to Oracle naming conventions , or use PascalCaseing for table/procedure/index/trigger names and under_score for fields and variables.

share|improve this answer
add comment

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