SQL formatting standards

In my last job, we worked on a very database-heavy application, and I developed some formatting standards so that we would all write SQL with a common layout. We also developed coding standards, but these are more platform-specific so I'll not go into them here.

I'm interested to know what other people use for SQL formatting standards. Unlike most other coding environments, I haven't found much of a consensus online for them.

To cover the main query types:

select
	ST.ColumnName1,
	JT.ColumnName2,
	SJT.ColumnName3
from 
    SourceTable ST
inner join JoinTable JT
	on JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT
	on ST.SourceTableID = SJT.SourceTableID
	and JT.Column3 = SJT.Column4
where
	ST.SourceTableID = X
	and JT.ColumnName3 = Y

There was some disagreement about line feeds after select, from and where. The intention on the select line is to allow other operators such as "top X" without altering the layout. Following on from that, simply keeping a consistent line feed after the key query elements seemed to result in a good level of readability.

Dropping the linefeed after the from and where would be an understandable revision. However, in queries such as the update below, we see that the line feed after the where gives us good column alignment. Similarly, a linefeed after group by or order by keeps our column layouts clear and easy to read.

update
	TargetTable
set
	ColumnName1 = @value,
	ColumnName2 = @value2
where
	Condition1 = @test

Finally, an insert:

insert into TargetTable (
	ColumnName1,
	ColumnName2,
	ColumnName3
) values (
	@value1,
	@value2,
	@value3
)

For the most part, these don't deviate that far from the way MS SQL Server Managements Studio / query analyser write out SQL, however they do differ.

I look forward to seeing whether there is any consensus in the Stack Overflow community on this topic. I'm constantly amazed how many developers can follow standard formatting for other languages and suddenly go so random when hitting SQL.

Late answer, but hopefully useful.

My experience working as part of the larger development team is that you can go ahead and define any standards you like, but the problem is actually enforcing these or making it very easy for developers to implement.

As developers we sometimes create something that works and then say “I’ll format it later”, but that later never comes.

Initially, we used SQL Prompt (it was great) for this, but then switched to ApexSQL Refactor, because it’s a free tool.

I'm late to the party, but I'll just add my preferred formatting style, which I must've learned from books and manuals: it's compact. Here's the sample SELECT statement:

SELECT  st.column_name_1, jt.column_name_2,
        sjt.column_name_3
FROM    source_table AS st
        INNER JOIN join_table AS jt USING (source_table_id)
        INNER JOIN second_join_table AS sjt ON st.source_table_id = sjt.source_table_id
                AND jt.column_3 = sjt.column_4
WHERE   st.source_table_id = X
AND     jt.column_name_3 = Y

In short: 8-space indentation, keywords in caps (although SO colours them better when in lowercase), no camelcase (pointless on Oracle), and line wraps when needed.

The UPDATE:

UPDATE  target_table
SET     column_name_1 = @value,
        column_name_2 = @value2
WHERE   condition_1 = @test

And the INSERT:

INSERT  INTO target_table (column_name_1, column_name_2,
                column_name_3)
VALUES  (@value1, @value2, @value3)

Now, let me be the first to admit that this style has it's problems. The 8-space indent means that ORDER BY and GROUP BY either misalign the indent, or split the word BY off by itself. It would also be more natural to indent the entire predicate of the WHERE clause, but I usually align following AND and OR operators at the left margin. Indenting after wrapped INNER JOIN lines is also somewhat arbitrary.

But for whatever reason, I still find it easier to read than the alternatives.

I'll finish with one of my more complex creations of late using this formatting style. Pretty much everything you'd encounter in a SELECT statement shows up in this one. (It's also been altered to disguise its origins, and I may have introduced errors in so doing.)

SELECT  term, student_id,
        CASE
            WHEN ((ft_credits > 0 AND credits >= ft_credits) OR (ft_hours_per_week > 3 AND hours_per_week >= ft_hours_per_week)) THEN 'F'
            ELSE 'P'
        END AS status
FROM    (
        SELECT  term, student_id,
                pm.credits AS ft_credits, pm.hours AS ft_hours_per_week,
                SUM(credits) AS credits, SUM(hours_per_week) AS hours_per_week
        FROM    (
                SELECT  e.term, e.student_id, NVL(o.credits, 0) credits,
                        CASE
                            WHEN NVL(o.weeks, 0) > 5 THEN (NVL(o.lect_hours, 0) + NVL(o.lab_hours, 0) + NVL(o.ext_hours, 0)) / NVL(o.weeks, 0)
                            ELSE 0
                        END AS hours_per_week
                FROM    enrollment AS e
                        INNER JOIN offering AS o USING (term, offering_id)
                        INNER JOIN program_enrollment AS pe ON e.student_id = pe.student_id AND e.term = pe.term AND e.offering_id = pe.offering_id
                WHERE   e.registration_code NOT IN ('A7', 'D0', 'WL')
                )
                INNER JOIN student_history AS sh USING (student_id)
                INNER JOIN program_major AS pm ON sh.major_code_1 = pm._major_code AND sh.division_code_1 = pm.division_code
        WHERE   sh.eff_term = (
                        SELECT  MAX(eff_term)
                        FROM    student_history AS shi
                        WHERE   sh.student_id = shi.student_id
                        AND     shi.eff_term <= term)
        GROUP   BY term, student_id, pm.credits, pm.hours
        )
ORDER   BY term, student_id

This abomination calculates whether a student is full-time or part-time in a given term. Regardless of the style, this one's hard to read.

I am of the opinion that so long as you can read the source code easily, the formatting is secondary. So long as this objective is achieved, there are a number of good layout styles that can be adopted.

The only other aspect that is important to me is that whatever coding layout/style you choose to adopt in your shop, ensure that it is consistently used by all coders.

Just for your reference, here is how I would present the example you provided, just my layout preference. Of particular note, the ON clause is on the same line as the join, only the primary join condition is listed in the join (i.e. the key match) and other conditions are moved to the where clause.

select
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3
from 
    SourceTable ST
inner join JoinTable JT on 
    JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT on 
    ST.SourceTableID = SJT.SourceTableID
where
        ST.SourceTableID = X
    and JT.ColumnName3 = Y
    and JT.Column3 = SJT.Column4

One tip, get yourself a copy of SQL Prompt from Red Gate. You can customise the tool to use your desired layout preferences, and then the coders in your shop can all use it to ensure the same coding standards are being adopted by everyone.

SELECT
    a.col1 				    AS [Column1]
    ,b.col2 				AS [Column2]
    ,c.col1 				AS [Column3]
FROM
    Table1 a
	INNER JOIN Table2 b 	ON b.Id = a.bId
	INNER JOIN Table3 c 	ON c.Id = a.cId
WHERE
	a.col     = X
	AND b.col = Y

Uses much more lines than a lot of the examples here, but I feel it's a lot easier to understand, enables quick removal of columns/clauses/tables. It helps to take advantage of a vertically-oriented monitor.

Late, but I'll throw my hat in the ring. It takes a bit longer to write, but I find patterns emerge with the vertical alignment that make it very readable once you're used to it.

SELECT ST.ColumnName1,
       JT.ColumnName2,
       SJT.ColumnName3,
       CASE WHEN condition1 = True 
             AND condition2 = True Then DoSomething
            Else DoSomethingElse
        END ColumnName4
  FROM SourceTable AS ST
 INNER
  JOIN JoinTable AS JT
    ON JT.SourceTableID = ST.SourceTableID
 INNER
  JOIN SecondJoinTable AS SJT
    ON ST.SourceTableID = SJT.SourceTableID
   AND JT.Column3 = SJT.Column4
  LEFT
  JOIN (SELECT Column5
          FROM Table4
       QUALIFY row_number() OVER
                 ( PARTITION BY pField1,
                                pField2
                       ORDER BY oField1
                 ) = 1
       ) AS subQry
    ON SJT.Column5 = subQry.Column5
 WHERE ST.SourceTableID = X
   AND JT.ColumnName3 = Y

Nice. As a Python programmer, here are my preferences:

Newlines after select, from and where only when it is needed for readability.

When code can be more compact and equally readable, I usually prefer the more compact form. Being able to fit more code in one screenful improves productivity.

select ST.ColumnName1, JT.ColumnName2, SJT.ColumnName3
from SourceTable ST
inner join JoinTable JT
    on JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT
    on ST.SourceTableID = SJT.SourceTableID
    and JT.Column3 = SJT.Column4
where ST.SourceTableID = X and JT.ColumnName3 = Y

Ultimately, this will be a judgment call that will be made during code review.

For insert, I would place the parenthesis differently:

insert into TargetTable (
    ColumnName1,
    ColumnName2,
    ColumnName3)
values (
    @value1,
    @value2,
    @value3)

The reasoning for this formatting is that if SQL used indentation for block structure (like Python), the parenthesis would not be needed. So, if indentation is used anyway, then parenthesis should have the minimum effect on the layout. This is achieved by placing them at the end of the lines.

I am working on writing an open-source SQL Formatter (SQL-Server-only at this stage) in C#, so I put the above queries through it.

It employs a similar strategy to the OP, namely that each 'section' has child elements indented beneath it. Where required, I add white space between sections to aid clarity – these wouldn't be added when there are no joins or minimal where conditions.

Result:

SELECT
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3

FROM SourceTable ST

INNER JOIN JoinTable JT
        ON JT.SourceTableID = ST.SourceTableID

INNER JOIN SecondJoinTable SJT
        ON ST.SourceTableID = SJT.SourceTableID
       AND ST.SourceTable2ID = SJT.SourceTable2ID

WHERE ST.SourceTableID = X
  AND JT.ColumnName3 = Y
  AND JT.Column3 = SJT.Column4

ORDER BY
    ST.ColumnName1

I would suggest the following style, based on the John's suggestion:

/*
<Query title>
<Describe the overall intent of the query>
<Development notes, or things to consider when using/interpreting the query>
*/
select
	ST.ColumnName1,
	JT.ColumnName2,
	SJT.ColumnName3
from 

	-- <Comment why this table is used, and why it's first in the list of joins>
	SourceTable ST
		
	-- <Comment why this join is made, and why it's an inner join>
	inner join JoinTable JT
		on ST.SourceTableID = JT.SourceTableID
		
	-- <Comment why this join is made, and why it's an left join>
	left join SecondJoinTable SJT
		on	ST.SourceTableID = SJT.SourceTableID
		and JT.Column3 = SJT.Column4
	
where
	
	-- comment why this filter is applied
	ST.SourceTableID = X
		
	-- comment why this filter is applied
	and JT.ColumnName3 = (
			select 
				somecolumn
			from 
				sometable
		)
;

Advantages:

• Comments are an essential part of making code readable and detecting mistakes.
• Adding -all- "on"-filters to the join avoids mistakes when changing from inner to left join.
• Placing the semicolon on a newline allows for easy adding/commenting of where clauses.

I tend to use a layout similar to yours, although I even go a few steps further, e.g.:

select
        ST.ColumnName1
    ,   JT.ColumnName2
    ,   SJT.ColumnName3
from
                SourceTable     ST
    
    inner join  JoinTable       JT
        on  JT.SourceTableID    =   ST.SourceTableID
    
    inner join  SecondJoinTable SJT
        on  ST.SourceTableID    =   SJT.SourceTableID
        
where
        ST.SourceTableID    =   X
    and JT.ColumnName3      =   Y
    and JT.Column3          =   SJT.Column4

Perhaps it looks a little over the top at first, but IMHO the use of tabulation in this way gives the cleanest, most systematic layout given the declarative nature of SQL.

You'll probably end up with all sorts of answers here. In the end, it's down to personal or team-agreed preferences.

I use a format similar to yours except that I put the ON keyword on the same line as the join and I put AND and OR operators at the end of lines so that all of my join/selection criteria line up nicely.

While my style is similar to John Sansom's, I disagree about putting join criteria in the WHERE clause. I think that it should be with the joined table so that it's organized and easy to find.

I also tend to put parentheses on new lines, aligned with the line above it and then indenting on the next line, although for short statements, I may just keep the parentheses on the original line. For example:

SELECT
     my_column
FROM
     My_Table
WHERE
     my_id IN
     (
          SELECT
               my_id
          FROM
               Some_Other_Table
          WHERE
               some_other_column IN (1, 4, 7)
     )

For CASE statements, I give a new line and indentation for each WHEN and ELSE, and I align the END back to the CASE:

CASE
     WHEN my_column = 1 THEN 'one'
     WHEN my_column = 2 THEN 'two'
     WHEN my_column = 3 THEN 'three'
     WHEN my_column = 4 THEN 'four'
     ELSE 'who knows'
END

The number of differing opinions is scary. This is what my organization uses:

 SELECT ST.ColumnName1,
        JT.ColumnName2,
        SJT.ColumnName3
   FROM SourceTable ST
  INNER JOIN JoinTable JT ON JT.SourceTableID = ST.SourceTableID
  INNER JOIN SecondJoinTable SJT ON ST.SourceTableID = SJT.SourceTableID 
        AND JT.Column3 = SJT.Column4
  WHERE ST.SourceTableID = X
    AND JT.ColumnName3 = Y

Maintaining the 8-character indent is key to readability IMHO.

Nobody's done common table expressions (CTEs) yet. Below incorporates it along with some other styles I use:

declare @tableVariable table (
    colA1 int,
    colA2 int,
    colB1 int,
    colB2 nvarchar(255),
    colB3 nvarchar(255),
    colB4 int,
    colB5 bit,
    computed int
);

with

    getSomeData as (

        select        st.colA1, sot.colA2
        from          someTable st
        inner join    someOtherTable sot on st.key = sot.key

    ),

    getSomeOtherData as (

        select        colB1, 
                      colB2, 
                      colB3,
                      colB4,
                      colB5,
                      computed =    case 
                                    when colB5 = 1 then 'here'
                                    when colB5 = 2 then 'there'
                                    end
        from          aThirdTable tt
        inner hash 
         join         aFourthTable ft
                      on tt.key1 = ft.key2
                      and tt.key2 = ft.key2
                      and tt.key3 = ft.key3

    )

    insert      @tableVariable (
                    colA1, colA2, colA2, 
                    colB1, colB2, colB3, colB4, colB5, 
                    computed 
                )
    select      colA1, colA2, 
                colB1, colB2, colB3, colB4, colB5, 
                computed 
    from        getSomeData data1
    join        getSomeOtherData data2

A few points on the CTE format:

• In my CTEs "with" is on a seperate line, and everything else in the cte is indented.
• My CTE names are long and descriptive. CTE's can get complex and descriptive names are very helpful.
• For some reason, I find myself preferring verbs for CTE names. Makes it seem more lively.
• Similar style with the parentheses as Javascript does with its braces. It's also how I do the braces in C#.

This simulates:

func getSomeData() {

    select        st.colA1, sot.colA2
    from          someTable st
    inner join    someOtherTable sot on st.key = sot.key

}

A few points besides the CTE format:

• Two tabs after "select" and other keywords. That leaves enough room for "inner join", "group by", etc. You can see one example above where that's not true. But "inner hash join" SHOULD look ugly. Nevertheless, on this point I'm probably going to experiment with some of the styles above in the future.
• Keywords are lowercase. Their colorization by the IDE and their special indentation status highlight them enough. I reserve uppercase for other things I want to emphasize based on local (business) logic.
• If there are few columns, I put them on one row (getSomeData). If there are a few more, I verticalize them (getSomeOtherData). If there is too much verticalization in one unit, I horizontalize some columns into the same line grouped by locally defined logic (the final insert-select segment). For instance, I would put school-level information on one line, student-level on another, etc.
• Especially when verticalizing, I prefer sql server's "varname = colname + something syntax" to "colname + something as varname".
• Double the last point if I'm dealing with a case statement.
• If a certain logic lends itself to a 'matrix' style, I will deal with the typing consequences. That's sort of what's going on with the case statement, where the 'whens' and 'then's are aligned.

I find that I am more settled on my CTE style than other areas. Haven't experimented with the styles more similar to that posed in the question. Probably will do someday and see how I like it. I'm probably cursed to be in an environment where it's a choice, though it's a fun curse to have.

If I am making changes to already written T-SQL, then I follow the already used convention (if there is one).

If I am writing from scratch or there is no convention, then I tend to follow your convention given in the question, except I prefer to use capital letters for keywords (just a personal preference for readability).

I think with SQL formatting as with other code format conventions, the important point is to have a convention, not what that convention is (within the realms of common sense of course!)

Yeah I can see the value of laying out your sql in some rigourously defined way, but surely the naming convention and your intent are far more important. Like 10 times more important.

Based on that my pet hates are tables prefixed by tbl, and stored procedures prefixed by sp - we know they're tables and SPs. Naming of DB objects is far more important than how many spaces there are

Just my $0.02 worths

I realise I am very late to this debate, but I would like to give my thoughts. I am definitely in favour of commas at the start of the line. Like you say Adam Ralph, it's easier to comment out a field and I also find it is more difficult to miss out a comma accidently when they are at the beginning, whilst this doesn't sound like a major issue. I have spent hours in the past trying to track down accidental syntax errors in lengthy T-SQL procedures, where I have accidently missed out a comma at the end of the line (I'm sure some of you have probably done this as well). I'm also in favour of aliasing as much as possible.

Overall, though, I realise it's all down to personal preference, what works for some doesn't for others. As long as you can read the code easily and each developer shows some consistency in their style throughout, I think that's most important.

I like:

SELECT ST.ColumnName1, JT.ColumnName2, SJT.ColumnName3 --leave all selected columns on the same line
FROM 
    SourceTable ST
INNER JOIN JoinTable JT ON JT.SourceTableID = ST.SourceTableID
INNER JOIN SecondJoinTable SJT --only splitting lines when more than 1 condition
    ON ST.SourceTableID = SJT.SourceTableID
    AND JT.Column3 = SJT.Column4
WHERE
    ST.SourceTableID = X
    and JT.ColumnName3 = Y

to get more code in a smaller viewing area. I also beleive that keywords should be in capitals

I agree whole heartily with your effort to standardize SQL formatting within your project and in general.

I also very much agree with your formatting choices. I have come up with nearly the same one, save I also indent the 'join' statements, and with them the 'on' statements one more indent.

Very much like the fact that you go for lower case on keywords - Who want's those SHOUTED OUT at you. I also prefer to lower case table aliases - makes for better readability

Very much like the fact that you use small indentation (4). I go with (3).

I nix the terms 'inner' and 'outer' as then are unnecessary.

Here is how would have formatted your select statement:

select
   st.ColumnName1,
   jt.ColumnName2,
   sjt.ColumnName3
from 
   SourceTable st
   join JoinTable jt on jt.SourceTableID = st.SourceTableID
   join SecondJoinTable sjt on
      st.SourceTableID = sjt.SourceTableID and
      jt.Column3 = sjt.Column4
where
   st.SourceTableID = X
   and jt.ColumnName3 = Y
;

Thanks for discussing this.

There are many good points in this thread. The one standard that I have been attempting to convince people to use is placing the comma on the same line before each column. Like so:

Select column1
   ,column2
   ,column3
   ,column4
   ,Column5 ...ect

Opposed to:

Select column1,
   column2,
   column3, ect...

The reason I prefer this practice is because, if necessary, you can comment out a line and there wont be a comma issue when you run it on account of the corresponding comma being commented out as well. I know that I saw another user in the thread that had done this as well but did not really point it out. Not a huge revelation to bring to the conversation but my two cents. Thanks

This is the format that I use. Please comment if it can be make better.

CREATE PROCEDURE [dbo].[USP_GetAllPostBookmarksByUserId]
	@id INT,
	@startIndex INT,
	@endIndex INT
AS
BEGIN
	
	SET NOCOUNT ON

	SELECT		*
	FROM
			(	SELECT		ROW_NUMBER() OVER ( ORDER BY P.created_date ) AS row_num, P.post_id, P.title, P.points, p.estimated_read_time, P.view_count, COUNT(1) AS "total_attempts" -- todo
				FROM		[dbo].[BOOKMARKED] B
				INNER JOIN	[dbo].[POST] P
				ON			B.entity_id = P.post_id
				INNER JOIN	[dbo].[ATTEMPTED] A
				ON			A.entity_id = P.post_id
				WHERE		B.user_id = 1 AND P.is_active = 1
				GROUP BY	P.post_id, P.title, P.points, p.estimated_read_time, P.view_count
			)	AS PaginatedResult
	WHERE		row_num >= @startIndex
	AND			row_num < @endIndex
	ORDER BY	row_num
	
END

My answer will be similar to the accepted answer by John Sansom answered Feb 6 '09 at 11:05. However, I will demonstrate some formatting options using SQLInForm plugin in NOTEPAD++, as opposed to his answer with SQL Prompt from Red Gate.

The SQLInForm plugin has 5 different profiles you can setup. Within the profile there are lots of settings available in both the FREE and PAID versions. An exhaustive list is below and you can see their plugin-help-general-options page online.

Instead of rambling about my preferences I considered it would be useful to present the SQLInForm options available. Some of my preferences are also noted below. At the end of my post is the formatted SQL Code used in the original post (original VS format1 VS format2).

Reading through other answers here-- I seem to be in the minority on a couple things. I like leading commas (Short Video Here)-- IMO, It is much easier to read when a new field is being selected. And also I like my Column1 with linebreak and not next to the SELECT.

---

Here is an overview with some of my preferences notes, considering a SELECT Statement. I would add screenshots of all 13 sections; But that is a lot of screenshots and I would just encourage you to the the free edition-- take some screenshots, and test the format controls. I will be testing out Pro edition soon; But based on the options it looks like it will be really helpful and for only $20.

SQLInForm Notepadd++: Options & Preferences

> 1. General (free) > >> DB: Any SQL, DB2/UDB, Oracle, MSAccess, SQL Server, Sybase, MYSQL, >> PostgreSQL, Informix, Teradata, Netezza SQL > >> [Smart Indent]= FALSE > > 2. Colors (free) > > 3. Keywords (PRO) > >> [Upper/LowerCase]> Keywords > > 4. Linebreaks> Lists (free) > >> [Before Comma]=TRUE 5 > >> [Move comma 2 cols to the left]= FALSE > > 5. Linebreaks>Select (PRO) > >> [JOIN> After JOIN]= FALSE > >> [JOIN> Before ON]= FALSE > >> (no change)--> [JOIN> Indent JOIN]; [JOIN> After ON] > > 6. Linebreaks> Ins/Upd/Del (PRO) > > 7. Linebreaks> Conditions (PRO) > >> CASE Statement--> [WHEN], [THEN], [ELSE] … certainly want to play >> with these settings and pick a good one > > 8. Alignment (PRO) > >> (no change)--> [JOIN> Indent JOIN]; [JOIN> After ON] > > 9. White Spaces (PRO) > >> (change?) Blank Lines [Remove All]=TRUE; [Keep All]; [Keep One] > > 10. Comments (PRO) > >> (change?) Line & Block--> [Linebreak Before/ After Block >> Comments]=TRUE; [Change Line Comments into Block]; [Block into Line] > > 11. Stored Proc (PRO) > > 12. Advanced (PRO) > >> (Could be useful) Extract SQL from Program Code--> [ExtractSQL] > > 13. License

---

SQL Code

The original query format.

select
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3
from 
    SourceTable ST
inner join JoinTable JT
    on JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT
    on ST.SourceTableID = SJT.SourceTableID
    and JT.Column3 = SJT.Column4
where
    ST.SourceTableID = X
    and JT.ColumnName3 = Y

CONVERSION PREFERRED FORMAT (option #1: JOIN no linebreak)

SELECT
    ST.ColumnName1
    , JT.ColumnName2
    , SJT.ColumnName3
FROM
    SourceTable ST
    inner join JoinTable JT 
		on JT.SourceTableID = ST.SourceTableID
    inner join SecondJoinTable SJT
		on ST.SourceTableID = SJT.SourceTableID
		and JT.Column3      = SJT.Column4
WHERE
    ST.SourceTableID   = X
    and JT.ColumnName3 = Y

CONVERSION PREFERRED FORMAT (option #2: JOIN with linebreak)

SELECT	
    ST.ColumnName1
    , JT.ColumnName2
    , SJT.ColumnName3
FROM
    SourceTable ST
    inner join
		JoinTable JT
		on JT.SourceTableID = ST.SourceTableID
    inner join
        SecondJoinTable SJT
		on ST.SourceTableID = SJT.SourceTableID
		and JT.Column3      = SJT.Column4
WHERE
    ST.SourceTableID   = X
    and JT.ColumnName3 = Y

Hope this helps.

Better late than never. I use a different style and adopted it from a very good SQL developer I used to work with. I right-align keywords and I don't use UPPERCASE letters for the ease of typing. The keywords will be highlighted by the editor and I don't see a need for them to be in the UPPERCASE unless you do lots of editing in text editors that does not support keyword-highlighting features. I don't try to make it compact, but rather more readable and vertically-aligned as much as possible. Here is an example of a select taken from @BenLaan answer written in my format:

select st.ColumnName1
       , jt.ColumnName2
       , sjt.ColumnName3
  from SourceTable st
         inner join
       JoinTable jt
         on jt.SourceTableID = st.SourceTableID
         inner join
       SecondJoinTable sjt
         on st.SourceTableID = sjt.SourceTableID
         and st.SourceTable2ID = sjt.SourceTable2ID
 where st.SourceTableID = X
       and jt.ColumnName3 = Y
       and jt.Column3 = sjt.Column4
 order by st.ColumnName1

Trying to conform all the team to follow the same formatting pattern is the hardest thing. I would follow any format, if everyone else followed the same way, but it has never been the same story.

UPDATE: Rewriting one of the complex queries mentioned in earlier posts:

select
       term
       , student_id
       , case
           when((ft_credits > 0 and credits >= ft_credits) or (ft_hours_per_week > 3 and hours_per_week >= ft_hours_per_week))
             then 'F'
           else 'P'
         end as status
  from (select term
               , student_id
               , pm.credits AS ft_credits
               , pm.hours AS ft_hours_per_week
               , SUM(credits) AS credits
               , SUM(hours_per_week) AS hours_per_week
          from (select e.term
                       , e.student_id
                       , nvl(o.credits, 0) credits
                       , case
                           when nvl(o.weeks, 0) > 5 
                             then (nvl(o.lect_hours, 0) + nvl(o.lab_hours, 0) + nvl(o.ext_hours, 0)) / nvl(o.weeks, 0)
                           else 0
                        end as hours_per_week
                  from enrollment as e
                         inner join 
                       offering as o using (term, offering_id)
                         inner join
                       program_enrollment as pe 
                         on e.student_id = pe.student_id 
                         and e.term = pe.term 
                         and e.offering_id = pe.offering_id
                 where e.registration_code not in ('A7', 'D0', 'WL')
                )
                  inner join 
                student_history as sh using (student_id)
                  inner join 
                program_major as pm 
                  on sh.major_code_1 = pm._major_code and sh.division_code_1 = pm.division_code
         where sh.eff_term = (select max(eff_term)
                                from student_history as shi
                               where sh.student_id = shi.student_id
                                     and shi.eff_term <= term)
         group by term, student_id, pm.credits, pm.hours
        )
 order by term, student_id

I like my SQL to be formatted like so, though as long as the intent is easily readable, most any format will work. I just really hate seeing statements created in the query designer and then left that way. If I am editing someone else procedure/view/function/trigger etc.., I will try to maintain the formatting already used (Unless it is really bad, then I will reformat the whole thing).

Select Statement

SELECT ST.ColumnName1, JT.ColumnName2, SJT.ColumnName3
  FROM SourceTable ST INNER JOIN
       JoinTable JT ON JT.SourceTableID = ST.SourceTableID 
       INNER JOIN
       SecondJoinTable SJT ON ST.SourceTableID = SJT.SourceTableID
                          AND JT.Column3 = SJT.Column4
WHERE (ST.SourceTableID = X)
  AND (JT.ColumnName3 = Y);

Update Statement

UPDATE TargetTable SET
       ColumnName1 = @value,
       ColumnName2 = @value2
 WHERE (Condition1 = @test);

Insert Statement

INSERT INTO TargetTable 
           (
             ColumnName1,
             ColumnName2,
             ColumnName3
           ) 
           values 
           (
             @value1,
             @value2,
             @value3
           );

My preferred style:

SELECT
  ST.ColumnName1,
  JT.ColumnName2,
  SJT.ColumnName3
FROM
  SourceTable ST
INNER JOIN
  JoinTable JT
ON
  JT.SourceTableID = ST.SourceTableID
INNER JOIN
  SecondJoinTable SJT
ON
  ST.SourceTableID = SJT.SourceTableID
WHERE
  ST.SourceTableID = X
AND
  JT.ColumnName3 = Y
AND
  JT.Column3 = SJT.Column4

SELECT st.ColumnName1
      ,jt.ColumnName2
      ,sjt.ColumnName3
FROM   SourceTable st
JOIN   JoinTable jt ON jt.SourceTableID = st.SourceTableID
JOIN   SecondJoinTable sjt ON SstT.SourceTableID = sjt.SourceTableID
                              AND jt.Column3 = sjt.Column4
WHERE  st.SourceTableID = X
       AND jt.ColumnName3 = Y

I use all caps for the actions words, joins or clauses, they stand out better. JOIN is the same as INNER JOIN so INNER does not need to be written out, it's assumed, write OUTER JOIN or LEFT JOIN when you need it. I also use lowere case for my alias names. Common out front cause if you comment out the last column you are stuck with a comma above and the query fails.

A hundred answers here already, but after much toing and froing over the years, this is what I've settled on:

SELECT      ST.ColumnName1
          , JT.ColumnName2
          , SJT.ColumnName3
 
FROM        SourceTable       ST
JOIN        JoinTable         JT  ON  JT.SourceTableID  =  ST.SourceTableID
JOIN        SecondJoinTable  SJT  ON  ST.SourceTableID  =  SJT.SourceTableID
                                  AND JT.Column3        =  SJT.Column4

WHERE       ST.SourceTableID  =  X
AND         JT.ColumnName3    =  Y

I know this can make for messy diffs as one extra table could cause me to re-indent many lines of code, but for my ease of reading I like it.

This is my personal SQL style guide. It is based on a couple of others, but has a few main stylistic features - lowercase keywords, no extraneous keywords (e.g. outer, inner, asc), and a "river".

Example SQL looks like this:

-- basic select example
select p.Name as ProductName
     , p.ProductNumber
     , pm.Name as ProductModelName
     , p.Color
     , p.ListPrice
  from Production.Product as p
  join Production.ProductModel as pm
    on p.ProductModelID = pm.ProductModelID
 where p.Color in ('Blue', 'Red')
   and p.ListPrice < 800.00
   and pm.Name like '%frame%'
 order by p.Name

-- basic insert example
insert into Sales.Currency (
    CurrencyCode
    ,Name
    ,ModifiedDate
)
values (
    'XBT'
    ,'Bitcoin'
    ,getutcdate()
)

-- basic update example
update p
   set p.ListPrice = p.ListPrice * 1.05
     , p.ModifiedDate = getutcdate()
  from Production.Product p
 where p.SellEndDate is null
   and p.SellStartDate is not null

-- basic delete example
delete cc
  from Sales.CreditCard cc
 where cc.ExpYear < '2003'
   and cc.ModifiedDate < dateadd(year, -1, getutcdate())

I think that having a good formatting rules are really important because you can spot and fix bugs easily. As it's said - "You’re writing code once, this code is read then 10000000 of times", so it always good to spend some time on formatting. The primary goals are:

• Make your code easier to read and understand
• Minimize the effort required to maintain or extend your code
• Reduce the need for users and developers of a system to consult secondary documentation sources such as code comments or software manuals

Some rules I always use:

• Always use .

It appears that most of you still work on monitors that only support 800x600. My monitors will do 1920x1080, so I want to use up all that space to the right.

how about this:

select col1, col2, col3
, case when x = 1 then 'answer1'
       else 'answer2'
  end
, col4, col5, col6, col7
from table1 t1
inner join table2 t2 on t1.col1 = t2.col1 and t1.col2 and t2.col2
where t1.col5 = 19 and t1.col7 = 'Bill Gates'