SQL 格式标准

SQL formatting standards

提问by Timbo

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.

在我的上一份工作中，我们开发了一个非常依赖数据库的应用程序，我开发了一些格式标准，以便我们都可以使用通用布局编写 SQL。我们还开发了编码标准，但这些标准更特定于平台，所以我不会在这里讨论它们。

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.

我很想知道其他人对 SQL 格式标准使用什么。与大多数其他编码环境不同，我还没有在网上找到很多关于它们的共识。

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, fromand 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.

关于select,from和之后的换行存在一些分歧where。选择行的目的是在不改变布局的情况下允许其他操作符，例如“top X”。之后，在关键查询元素之后简单地保持一致的换行似乎会导致良好的可读性。

Dropping the linefeed after the fromand wherewould be an understandable revision. However, in queries such as the updatebelow, we see that the line feed after the wheregives us good column alignment. Similarly, a linefeed after group byor order bykeeps our column layouts clear and easy to read.

在from和之后删除换行符where将是一个可以理解的修订。但是，在如下查询update中，我们看到 之后的换行符where为我们提供了良好的列对齐。同样，在group by或order by保持我们的列布局清晰易读之后换行。

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

Finally, an insert:

最后，一个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 dodiffer.

在大多数情况下，这些与 MS SQL Server Management Studio/ 查询分析器编写 SQL的方式并没有太大的不同，但它们确实有所不同。

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.

我期待看到 Stack Overflow 社区中是否就这个话题达成共识。我一直很惊讶有多少开发人员可以遵循其他语言的标准格式，而在使用 SQL 时突然变得如此随意。

采纳答案by John Sansom

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 ONclause 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 whereclause.

仅供您参考，以下是我将如何呈现您提供的示例，只是我的布局偏好。需要特别注意的是，ON子句与 位于同一行，在join连接中只列出主要连接条件（即键匹配），其他条件移动到where子句中。

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 Promptfrom 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.

一个提示，从Red Gate获取一份SQL Prompt副本。您可以自定义该工具以使用您想要的布局首选项，然后您商店中的编码员都可以使用它来确保每个人都采用相同的编码标准。

回答by John Emeres

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.

最初，我们为此使用了 SQL Prompt（它很棒），但后来切换到ApexSQL Refactor，因为它是一个免费工具。

回答by yukondude

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 SELECTstatement:

我迟到了，但我会添加我喜欢的格式样式，我一定是从书籍和手册中学到的：它很紧凑。这是示例SELECT语句：

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.

简而言之：8 个空格缩进，大写关键字（尽管小写时它们的颜色更好），没有驼峰式（在 Oracle 上毫无意义），以及在需要时换行。

The UPDATE:

的UPDATE：

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

And the INSERT:

和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 BYand GROUP BYeither misalign the indent, or split the word BYoff by itself. It would also be more natural to indent the entire predicate of the WHEREclause, but I usually align following ANDand ORoperators at the left margin. Indenting after wrapped INNER JOINlines is also somewhat arbitrary.

现在，让我第一个承认这种风格有问题。8个空格缩进装置，其ORDER BY与GROUP BY任一不对齐的缩进，或分裂字BY本身关闭。缩进WHERE子句的整个谓词也会更自然，但我通常在左边距对齐后面的AND和OR运算符。换INNER JOIN行后缩进也有些随意。

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 SELECTstatement shows up in this one. (It's also been altered to disguise its origins, and I may have introduced errors in so doing.)

我将使用这种格式样式完成我最近的一个更复杂的创作。您在SELECT声明中遇到的几乎所有内容都出现在此声明中。（它也被修改以掩盖它的起源，我可能在这样做时引入了错误。）

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.

这种可憎的东西计算学生在给定的学期是全日制还是兼职。无论风格如何，这本书都很难阅读。

回答by ddaa

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

好的。作为一名 Python 程序员，以下是我的偏好：

Newlines after select, fromand whereonly when it is needed for readability.

之后的换行符select，from并且where仅在可读性需要时才换行。

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，我会以不同的方式放置括号：

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.

这种格式的原因是如果 SQL 对块结构使用缩进（如 Python），则不需要括号。因此，如果无论如何都使用缩进，那么括号对布局的影响应该最小。这是通过将它们放置在行尾来实现的。

回答by Error_2646

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

回答by RossBille

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.

使用的行比这里的许多示例多得多，但我觉得它更容易理解，可以快速删除列/子句/表格。它有助于利用垂直定向的显示器。

回答by Ben Laan

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.

我正在用 C# 编写开源 SQL 格式化程序（现阶段仅限 SQL Server），因此我通过它完成了上述查询。

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.

它采用与 OP 类似的策略，即每个“部分”在其下方都有缩进的子元素。如果需要，我会在部分之间添加空白以帮助清晰——当没有连接或最小 where 条件时，不会添加这些空白。

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

回答by Wouter

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.

优点：
- 注释是使代码可读和检测错误的重要部分。
- 将 -all- "on"-filters 添加到连接可避免从内部连接更改为左连接时出错。
- 将分号放在换行符上可以轻松添加/注释 where 子句。

回答by Adam Ralph

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.

也许一开始看起来有点夸张，但恕我直言，鉴于 SQL 的声明性质，以这种方式使用制表提供了最干净、最系统的布局。

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

你可能会在这里得到各种各样的答案。最后，这取决于个人或团队同意的偏好。

回答by Tom H

I use a format similar to yours except that I put the ONkeyword on the same line as the join and I put ANDand ORoperators at the end of lines so that all of my join/selection criteria line up nicely.

我使用与您类似的格式，不同之处在于我将ON关键字与连接放在同一行，并将AND和OR运算符放在行尾，以便我的所有连接/选择条件都很好地对齐。

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

虽然我的风格与 John Sansom 的风格相似，但我不同意在WHERE子句中加入连接标准。我认为它应该与连接表一起使用，以便它有条理且易于查找。

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 CASEstatements, I give a new line and indentation for each WHENand ELSE, and I align the ENDback to the CASE:

对于CASE语句，我为每个WHEN和提供了一个新行和缩进ELSE，并将END背面与 对齐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