ANSI JOIN 与非 ANSI JOIN 查询的执行方式是否不同?

2022-01-30 00:00:00 join sql tsql sql-server

我的业务逻辑包含在大约 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 所指出的，它确实倾向于避免意外的交叉连接.

我本人反对 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.

相关文章