SQL ANSI JOIN 与非 ANSI JOIN 查询的性能会不同吗？

Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?

提问by abatishchev

I have my business-logic in ~7000 lines of T-SQL stored procedures, and most of them has next JOIN syntax:

我在大约 7000 行 T-SQL 存储过程中有我的业务逻辑，其中大多数都有下一个 JOIN 语法：

SELECT A.A, B.B, C.C
FROM aaa AS A, bbb AS B, ccc AS C
WHERE
    A.B = B.ID
AND B.C = C.ID
AND C.ID = @param

Will I get performance growth if I will replace such query with this:

如果我将这样的查询替换为：

SELECT A.A, B.B, C.C
FROM aaa AS A
JOIN bbb AS B
   ON A.B = B.ID
JOIN ccc AS C
   ON B.C = C.ID
   AND C.ID = @param

Or they are the same?

或者他们是一样的？

回答by Cruachan

The two queries are the same, except the second is ANSI-92 SQL syntax and the first is the older SQL syntax which didn't incorporate the join clause. They should produce exactly the same internal query plan, although you may like to check.

这两个查询是相同的，除了第二个是 ANSI-92 SQL 语法，第一个是没有合并 join 子句的旧 SQL 语法。它们应该生成完全相同的内部查询计划，尽管您可能想检查一下。

You should use the ANSI-92 syntax for several of reasons

出于多种原因，您应该使用 ANSI-92 语法

• The use of the JOIN clause separates the relationship logic from the filter logic (the WHERE) and is thus cleaner and easier to understand.
• It doesn't matter with this particular query, but there are a few circumstances where the older outer join syntax (using + ) is ambiguous and the query results are hence implementation dependent - or the query cannot be resolved at all. These do not occur with ANSI-92
• It's good practice as most developers and dba's will use ANSI-92 nowadays and you should follow the standard. Certainly all modern query tools will generate ANSI-92.
• As pointed out by @gbn, it does tend to avoid accidental cross joins.

• JOIN 子句的使用将关系逻辑与过滤器逻辑（WHERE）分开，因此更清晰、更容易理解。
• 这个特定查询无关紧要，但在某些情况下，旧的外连接语法（使用 + ）不明确，因此查询结果取决于实现 - 或者根本无法解析查询。这些不会发生在 ANSI-92
• 这是一种很好的做法，因为现在大多数开发人员和 dba 将使用 ANSI-92，您应该遵循该标准。当然，所有现代查询工具都会生成 ANSI-92。
• 正如@gbn 所指出的，它确实倾向于避免意外的交叉连接。

Myself I resisted ANSI-92 for some time as there is a slight conceptual advantage to the old syntax as it's a easier to envisage the SQL as a mass Cartesian join of all tables used followed by a filtering operation - a mental technique that can be useful for grasping what a SQL query is doing. However I decided a few years ago that I needed to move with the times and after a relatively short adjustment period I now strongly prefer it - predominantly because of the first reason given above. The only place that one should depart from the ANSI-92 syntax, or rather not use the option, is with natural joins which are implicitly dangerous.

我自己一段时间以来一直抵制 ANSI-92，因为旧语法在概念上略有优势，因为将 SQL 设想为所有使用的表的大规模笛卡尔连接，然后是过滤操作 - 一种有用的心理技术用于掌握 SQL 查询在做什么。然而，几年前我决定我需要与时俱进，经过一段相对较短的调整期后，我现在非常喜欢它——主要是因为上面给出的第一个原因。唯一应该偏离 ANSI-92 语法或不使用该选项的地方是使用隐含危险的自然连接。

回答by onedaywhen

The second construct is known as the "infixed join syntax" in the SQL community. The first construct AFAIK doesn't have widely accepted name so let's call it the 'old style' inner join syntax.

第二种结构在 SQL 社区中称为“中缀连接语法”。第一个构造 AFAIK 没有被广泛接受的名称，所以我们称之为“旧式”内部连接语法。

The usual arguments go like this:

通常的论点是这样的：

Pros of the 'Traditional' syntax: the predicates are physically grouped together in the WHEREclause in whatever order which makes the query generally, and n-ary relationships particularly, easier to read and understand (the ONclauses of the infixed syntax can spread out the predicates so you have to look for the appearance of one table or column over a visual distance).

“传统”语法的优点：谓词WHERE以任何顺序在子句中物理组合在一起，这使得查询一般，尤其是 n 元关系，更容易阅读和理解（ON中缀语法的子句可以将谓词展开所以你必须在视觉距离内寻找一个表格或一列的外观）。

Cons of the 'Traditional' syntax: There is no parse error when omitting one of the 'join' predicates and the result is a Cartesian product (known as a CROSS JOINin the infixed syntax) and such an error can be tricky to detect and debug. Also, 'join' predicates and 'filtering' predicates are physically grouped together in the WHEREclause, which can cause them to be confused for one another.

“传统”语法的缺点：省略其中一个“连接”谓词时不会出现解析错误，结果是笛卡尔积（CROSS JOIN在中缀语法中称为 a ），这样的错误很难检测和调试。此外，“join”谓词和“filtering”谓词在WHERE子句中物理组合在一起，这可能导致它们相互混淆。

回答by TwoEdgedSword

OK, they execute the same. That's agreed. Unlike many I use the older convention. That SQL-92 is "easier to understand" is debatable. Having written programming languages for pushing 40 years (gulp) I know that 'easy to read' begins first, before any other convention, with 'visual acuity' (misapplied term here but it's the best phrase I can use). When reading SQL the FIRST thing you mind cares about is what tables are involved and then which table (most) defines the grain. Then you care about relevant constraints on the data, then the attributes selected. While SQL-92 mostly separates these ideas out, there are so many noise words, the mind's eye has to interpret and deal with these and it makes reading the SQL slower.

好的，他们执行相同的。那是同意的。与许多人不同，我使用旧的约定。SQL-92“更容易理解”是有争议的。已经编写了 40 年的编程语言 (gulp)，我知道“易于阅读”首先是在任何其他约定之前，“视觉敏锐度”（此处误用了术语，但这是我可以使用的最佳短语）。在阅读 SQL 时，您首先关心的是涉及哪些表，然后是哪个表（大多数）定义了粒度。然后你关心数据的相关约束，然后是选择的属性。虽然 SQL-92 主要将这些想法分开，但有太多干扰词，头脑必须解释和处理这些，这使得阅读 SQL 变慢。

SELECT Mgt.attrib_a   AS attrib_a
      ,Sta.attrib_b   AS attrib_b
      ,Stb.attrib_c   AS attrib_c
FROM   Main_Grain_Table  Mgt
      ,Surrounding_TabA  Sta
      ,Surrounding_tabB  Stb
WHERE  Mgt.sta_join_col  = Sta.sta_join_col
AND    Mgt.stb_join_col  = Stb.stb_join_col
AND    Mgt.bus_logic_col = 'TIGHT'

Visual Acuity! Put the commas for new attributes in front It makes commenting code easier too Use a specific case for functions and keywords Use a specific case for tables Use a specific case for attributes Vertically Line up operators and operations Make the first table(s) in the FROM represent the grain of the data Make the first tables of the WHERE be join constraints and let the specific, tight constraints float to the bottom. Select 3 character alias for ALL tables in your database and use the alias EVERYWHERE you reference the table. You should use that alias as a prefix for (many) indexes on that table as well. 6 of 1 1/2 dozen of another, right? Maybe. But even if you're using ANSI-92 convention (as I have and in cases will continue to do) use visual acuity principles, verticle alignment to let your mind's eye avert to the places you want to see and and easily avoid things (particularly noise words) you don't need to.

视力！将新属性的逗号放在前面 这也使注释代码更容易 为函数和关键字使用特定情况 为表使用特定情况 为属性使用特定情况 垂直 排列运算符和操作 在 FROM 中制作第一个表表示数据的粒度 使 WHERE 的第一个表成为连接约束，并让特定的、严格的约束浮动到底部。为数据库中的所有表选择 3 个字符的别名，并在引用该表的任何地方使用别名。您还应该使用该别名作为该表上（许多）索引的前缀。1 1/2 打中的 6 个，对吧？也许。但即使您使用 ANSI-92 约定（正如我所拥有的，并且在某些情况下将继续使用）使用视力原则，垂直对齐让您的头脑'

回答by sisve

Execute both and check their query plans. They shouldbe equal.

执行两者并检查它们的查询计划。他们应该是平等的。

回答by OMG Ponies

The two queries areequal - the first is using non-ANSI JOIN syntax, the 2nd is ANSI JOIN syntax. I recommend sticking with the ANSI JOIN syntax.

这两个查询是相等的——第一个是使用非 ANSI JOIN 语法，第二个是 ANSI JOIN 语法。我建议坚持使用 ANSI JOIN 语法。

And yes, LEFT OUTER JOINs (which, btw are also ANSI JOIN syntax) are what you want to use when there's a possibility that the table you're joining to might not contain any matching records.

是的，LEFT OUTER JOIN（顺便说一句也是ANSI JOIN 语法）是当您要加入的表可能不包含任何匹配记录时要使用的。

Reference: Conditional Joins in SQL Server

参考：SQL Server 中的条件联接

回答by John

In my mind the FROM clause is where I decide what columns I need in the rows for my SELECT clause to work on. It is where a business rule is expressed that will bring onto the same row, values needed in calculations. The business rule can be customers who have invoices, resulting in rows of invoices including the customer responsible. It could also be venues in the same postcode as clients, resulting in a list of venues and clients that are close together.

在我看来，FROM 子句是我决定我的 SELECT 子句在行中需要处理哪些列的地方。它是表达业务规则的地方，它将带来计算中所需的值。业务规则可以是拥有发票的客户，从而产生包括负责客户在内的发票行。它也可以是与客户位于同一邮政编码的场所，从而生成距离较近的场所和客户的列表。

It is where I work out the centricity of the rows in my result set. After all, we are simply shown the metaphor of a list in RDBMSs, each list having a topic (the entity) and each row being an instance of the entity. If the row centricity is understood, the entity of the result set is understood.

这是我计算结果集中行的中心性的地方。毕竟，我们只是简单地展示了 RDBMS 中列表的比喻，每个列表都有一个主题（实体），每一行都是实体的一个实例。如果了解行中心性，则了解结果集的实体。

The WHERE clause, which conceptually executes after the rows are defined in the from clause, culls rows not required (or includes rows that are required) for the SELECT clause to work on.

WHERE 子句概念上在 from 子句中定义行之后执行，剔除 SELECT 子句处理不需要的行（或包括需要的行）。

Because join logic can be expressed in both the FROM clause and the WHERE clause, and because the clauses exist to divide and conquer complex logic, I choose to put join logic that involves values in columns in the FROM clause because that is essentially expressing a business rule that is supported by matching values in columns.

因为连接逻辑可以在FROM子句和WHERE子句中表达，并且因为子句的存在是为了分治复杂的逻辑，所以我选择将涉及值的连接逻辑放在FROM子句中，因为这本质上是表达一个业务列中的匹配值支持的规则。

i.e. I won't write a WHERE clause like this:

即我不会写这样的 WHERE 子句：

 WHERE Column1 = Column2

I will put that in the FROM clause like this:

我会像这样把它放在 FROM 子句中：

 ON Column1 = Column2

Likewise, if a column is to be compared to external values (values that may or may not be in a column) such as comparing a postcode to a specific postcode, I will put that in the WHERE clause because I am essentially saying I only want rows like this.

同样，如果要将列与外部值（列中可能存在或不存在的值）进行比较，例如将邮政编码与特定邮政编码进行比较，我会将其放在 WHERE 子句中，因为我基本上是说我只想要像这样的行。

i.e. I won't write a FROM clause like this:

即我不会写这样的 FROM 子句：

 ON PostCode = '1234'

I will put that in the WHERE clause like this:

我将把它放在 WHERE 子句中，如下所示：

 WHERE PostCode = '1234'

回答by Tegiri Nenashi

ANSI syntax does enforce neither predicate placement in the proper clause (be that ON or WHERE), nor the affinity of the ON clause to adjacent table reference. A developer is free to write a mess like this

ANSI 语法既不强制将谓词放置在适当的子句（即 ON 或 WHERE）中，也不强制 ON 子句与相邻表引用的关联。开发人员可以随意写出这样的乱七八糟的东西

SELECT
   C.FullName,
   C.CustomerCode,
   O.OrderDate,
   O.OrderTotal,
   OD.ExtendedShippingNotes
FROM
   Customer C
   CROSS JOIN Order O
   INNER JOIN OrderDetail OD
      ON C.CustomerID = O.CustomerID
      AND C.CustomerStatus = 'Preferred'
      AND O.OrderTotal > 1000.0
WHERE
   O.OrderID = OD.OrderID;

Speaking of query tools who "will generate ANSI-92", I'm commenting here because it generated

说到“会生成ANSI-92”的查询工具，我在这里评论是因为它生成了

SELECT 1
   FROM DEPARTMENTS C
        JOIN EMPLOYEES A
             JOIN JOBS B
     ON C.DEPARTMENT_ID = A.DEPARTMENT_ID
     ON A.JOB_ID = B.JOB_ID

The only syntax that escapes conventional "restrict-project-cartesian product"is outer join. This operation is more complicated because it is not associative (both with itself and with normal join). One have to judiciously parenthesize query with outer join, at least. However, it is an exotic operation; if you are using it too often I suggest taking relational database class.

逃避传统“限制项目笛卡尔积”的唯一语法是外连接。这个操作更复杂，因为它不是关联的（与自身和普通连接）。至少必须明智地使用外连接将查询括起来。然而，这是一个奇特的操作；如果您经常使用它，我建议您参加关系数据库课程。