ANSI JOIN与非ANSI JOIN查询的执行方式会有所不同吗? [英] Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?

问题描述

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

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

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

还是一样?

推荐答案

这两个查询是相同的，不同的是第二个查询是ANSI-92 SQL语法，而第一个是未合并join子句的较旧的SQL语法.尽管您可能想检查一下，但它们应该产生完全相同的内部查询计划.

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语法

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

• JOIN子句的使用分隔 关系逻辑 过滤逻辑(WHERE)，因此 更干净，更容易理解.
• 此特定查询无关紧要，但是在某些情况下，较旧的外部联接语法(使用+)含糊不清，因此查询结果取决于实现方式-或根本无法解析查询.这些在ANSI-92中不会发生
• 这是一个好习惯，因为当今大多数开发人员和dba都将使用ANSI-92，因此您应该遵循该标准.当然，所有现代查询工具都会生成ANSI-92.
• 如@gbn所指出的那样，它的确可以避免意外的交叉连接.

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

我本人抵制ANSI-92一段时间了，因为旧语法在概念上有一点优势，因为将SQL想象成所有使用的表的大规模笛卡尔联接，然后进行过滤操作会更容易，这是一种心理技术对于掌握SQL查询的功能很有用.但是，几年前，我决定需要与时俱进，经过一段较​​短的调整后，我现在非常喜欢它-主要是因为上面提到的第一个原因.唯一应该偏离ANSI-92语法或者不使用该选项的地方是自然连接，这是隐式的危险.

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 JOIN与非ANSI JOIN查询的执行方式会有所不同吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！