为什么 SQL ANSI-92 标准没有比 ANSI-89 更好地采用？

Why isn't SQL ANSI-92 standard better adopted over ANSI-89?

提问by Patrick Harrington

At every company I have worked at, I have found that people are still writing their SQL queries in the ANSI-89 standard:

在我工作过的每家公司，我发现人们仍在按照 ANSI-89 标准编写 SQL 查询：

select a.id, b.id, b.address_1
from person a, address b
where a.id = b.id

rather than the ANSI-92 standard:

而不是 ANSI-92 标准：

select a.id, b.id, b.address_1
from person a
inner join address b
on a.id = b.id

For an extremely simple query like this, there's not a big difference in readability, but for large queries I find that having my join criteria grouped in with listing out the table makes it much easier to see where I might have issues in my join, and let's me keep all my filtering in my WHERE clause. Not to mention that I feel that outer joins are much intuitive than the (+) syntax in Oracle.

对于像这样的极其简单的查询，可读性没有太大区别，但是对于大型查询，我发现将我的连接条件分组并列出表可以更容易地查看我的连接中可能存在问题的位置，并且让我将所有过滤保留在 WHERE 子句中。更不用说我觉得外连接比 Oracle 中的 (+) 语法直观得多。

As I try to evangelize ANSI-92 to people, are there any concrete performance benefits in using ANSI-92 over ANSI-89? I would try it on my own, but the Oracle setups we have here don't allow us to use EXPLAIN PLAN - wouldn't want people to try to optimize their code, would ya?

当我试图向人们宣传 ANSI-92 时，使用 ANSI-92 比使用 ANSI-89 有什么具体的性能优势吗？我会自己尝试，但是我们这里的 Oracle 设置不允许我们使用 EXPLAIN PLAN - 不希望人们尝试优化他们的代码，是吗？

采纳答案by Bill Karwin

According to "SQL Performance Tuning" by Peter Gulutzan and Trudy Pelzer, of the six or eight RDBMS brands they tested, there was no difference in optimization or performance of SQL-89 versus SQL-92 style joins. One can assume that most RDBMS engines transform the syntax into an internal representation before optimizing or executing the query, so the human-readable syntax makes no difference.

根据 Peter Gulutzan 和 Trudy Pelzer 的“SQL Performance Tuning”，在他们测试的六个或八个 RDBMS 品牌中，SQL-89 与 SQL-92 样式连接的优化或性能没有差异。可以假设大多数 RDBMS 引擎在优化或执行查询之前将语法转换为内部表示，因此人类可读的语法没有区别。

I also try to evangelize the SQL-92 syntax. Sixteen years after it was approved, it's about time people start using it! And all brands of SQL database now support it, so there's no reason to continue to use the nonstandard (+)Oracle syntax or *=Microsoft/Sybase syntax.

我还尝试传播 SQL-92 语法。在它获得批准 16 年后，是人们开始使用它的时候了！现在所有品牌的 SQL 数据库都支持它，所以没有理由继续使用非标准的(+)Oracle 语法或*=Microsoft/Sybase 语法。

As for why it's so hard to break the developer community of the SQL-89 habit, I can only assume that there's a large "base of the pyramid" of programmers who code by copy & paste, using ancient examples from books, magazine articles, or another code base, and these people don't learn new syntax abstractly. Some people pattern-match, and some people learn by rote.

至于为什么打破 SQL-89 习惯的开发者社区如此困难，我只能假设有一大群程序员通过复制和粘贴来编码，使用书籍、杂志文章中的古老例子，或其他代码库，这些人不会抽象地学习新语法。有些人模式匹配，有些人死记硬背。

I am gradually seeing people using SQL-92 syntax more frequently than I used to, though. I've been answering SQL questions online since 1994.

不过，我逐渐看到人们比以前更频繁地使用 SQL-92 语法。自 1994 年以来，我一直在网上回答 SQL 问题。

回答by Bill Karwin

Well the ANSI092 standard includes some pretty heinous syntax. Natural Joinsare one and the USING Clause is another. IMHO, the addition of a column to a table shouldn't break code but a NATURAL JOIN breaks in a most egregious fashion. The "best" way to break is by compilation error. For example if you SELECT * somewhere, the addition of a column couldfail to compile. The next best way to fail would be a run time error. It's worse because your users may see it, but it still gives you a nice warning that you've broken something. If you use ANSI92 and write queries with NATURAL joins, it won't break at compile time and it won't break at run time, the query will just suddenly start producing wrong results. These types of bugs are insidious. Reports go wrong, potentially financial disclosure are incorrect.

ANSI092 标准包含一些非常令人发指的语法。自然连接是一个，而 USING 子句是另一个。恕我直言，向表中添加一列不应破坏代码，但 NATURAL JOIN 以最令人震惊的方式破坏。破解的“最佳”方法是编译错误。例如，如果您在某处 SELECT *，则添加一列可能编译失败。下一个最好的失败方式是运行时错误。更糟糕的是，您的用户可能会看到它，但它仍然会给您一个很好的警告，表明您已经破坏了某些东西。如果您使用 ANSI92 并使用 NATURAL 连接编写查询，它不会在编译时中断，也不会在运行时中断，查询会突然开始产生错误的结果。这些类型的错误是阴险的。报告出错，潜在的财务披露不正确。

For those unfamiliar with NATURAL Joins. They join two tables on every column name that exists in both tables. Which is really cool when you have a 4 column key and you're sick of typing it. The problem comes in when Table1 has a pre-existing column named DESCRIPTION and you add a new column to Table2 named, oh I don't know, something innocuous like, mmm, DESCRIPTION and now you're joining the two tables on a VARCHAR2(1000) field that is free form.

对于那些不熟悉 NATURAL Joins 的人。它们在两个表中存在的每个列名上连接两个表。当你有一个 4 列的键并且你厌倦了输入它时，这真的很酷。当 Table1 有一个预先存在的名为 description 的列并且您向名为的 Table2 添加一个新列时，问题就出现了，哦，我不知道，一些无害的东西，比如，嗯，描述，现在您正在 VARCHAR2 上加入这两个表(1000) 字段是自由格式。

The USING clause can lead to total ambiguity in addition to the problem described above. In another SO post, someone showed this ANSI-92 SQL and asked for help reading it.

除了上述问题之外，USING 子句还可能导致完全歧义。在另一篇SO post 中，有人展示了这个 ANSI-92 SQL 并寻求帮助阅读它。

SELECT c.* 
FROM companies AS c 
JOIN users AS u USING(companyid) 
JOIN jobs AS j USING(userid) 
JOIN useraccounts AS us USING(userid) 
WHERE j.jobid = 123

This is completely ambiguous. I put a UserID column in both Companies and user tables and there's no complaint. What if the UserID column in companies is the ID of the last person to modify that row?

这是完全模棱两可的。我在公司和用户表中都放置了一个 UserID 列，没有任何抱怨。如果公司中的 UserID 列是最后修改该行的人的 ID 怎么办？

I'm serious, Can anyone explain why such ambiguity was necessary? Why is it built straight into the standard?

我是认真的，谁能解释为什么有必要这么含糊？为什么它直接内置到标准中？

I think Bill is correct that there is a large base of developer who copy/paste there way through coding. In fact, I can admit that I'm kind of one when it comes to ANSI-92. Every example I ever saw showed multiple joins being nested in parentheses. Honesty, that makes picking out the tables in the sql difficult at best. But then an SQL92 evangilist explained that would actually force a join order. JESUS... all those Copy pasters I've seen are now actually forcing a join order - a job that's 95% of the time better left to optimizers especiallya copy/paster.

我认为比尔是正确的，有大量的开发人员通过编码复制/粘贴到那里。事实上，我可以承认，当谈到 ANSI-92 时，我是一种。我见过的每个例子都显示了多个连接嵌套在括号中。老实说，这使得在 sql 中挑选表充其量是困难的。但是后来一位 SQL92 布道者解释说，这实际上会强制执行连接顺序。耶稣……我见过的所有复制粘贴器现在实际上都在强制执行连接顺序——这项工作 95% 的时间最好留给优化人员，尤其是复制/粘贴器。

Tomalak got it right when he said,

托马拉克说得对，他说，

people don't switch to new syntax just because it is there

人们不会仅仅因为它存在就切换到新语法

It has to give me something and I don't see an upside. And if there is an upside, the negatives are an albatross too big to be ignored.

它必须给我一些东西，我看不到任何好处。如果有好处，那么坏处就是巨大的信天翁，不容忽视。

回答by Tomalak

A few reasons come to mind:

想到几个原因：

• people do it out of habit
• people are lazy and prefer the "old style" joins because they involve less typing
• beginners often have their problems wrapping their heads around the SQL-92 join syntax
• people don't switch to new syntax just because it is there
• people are unaware of the benefits the new (if you want to call it that) syntax has, primarily that it enables you to filter a table beforeyou do an outer join, and not after it when all you have is the WHERE clause.

• 人们习惯于这样做
• 人们很懒惰，更喜欢“旧式”连接，因为它们涉及较少的打字
• 初学者经常会遇到围绕 SQL-92 连接语法的问题
• 人们不会仅仅因为它存在就切换到新语法
• 人们不知道新的（如果你想这样称呼它）语法的好处，主要是它使你能够在执行外连接之前过滤表，而不是在只有 WHERE 子句时过滤表。

For my part, I do all my joins in the SQL-92 syntax, and I convert code where I can. It's the cleaner, more readable and powerful way to do it. But it's hard to convince someone to use the new style, when they think it hurts them in terms of more typing work while not changing the query result.

就我而言，我使用 SQL-92 语法进行所有连接，并尽可能转换代码。这是一种更简洁、更具可读性和更强大的方式来做到这一点。但是很难说服某人使用新样式，因为他们认为这会在不更改查询结果的情况下进行更多的打字工作。

回答by Roger Bold

In response to the NATURAL JOIN and USING post above.

响应上面的 NATURAL JOIN 和 USING 帖子。

WHY would you ever see the need to use these - they weren't available in ANSI-89 and were added for ANSI-92 as what I can only see as a shortcut.

为什么你会看到需要使用这些 - 它们在 ANSI-89 中不可用，并且被添加到 ANSI-92 作为我只能看到的快捷方式。

I would never leave a join to chance and would always specify the table/alias and id.

我永远不会留下连接机会，并且总是指定表/别名和 id。

For me, the only way to go is ANSI-92. It is more verbose and the syntax isn't liked by ANSI-89 followers but it neatly separates your JOINS from your FILTERING.

对我来说，唯一的出路是 ANSI-92。它更冗长，ANSI-89 追随者不喜欢它的语法，但它巧妙地将您的 JOINS 与您的 FILTERING 分开。

回答by HLGEM

First let me say that in SQL Server the outer join syntax (*=) does not give correct results all the time. There are times when it interprets that as a cross join and not an outer join. So right there is a good reason to stop using it. And that outer join syntax is a deprecated feature and will not be in the next version of SQL Server after SQL Server 2008. You'll still be able to do the inner joins but why on earth would anyone want to? They are unclear and much much harder to maintain. You don't easily know what is part of the join and what is really just the where clause.

首先让我说，在 SQL Server 中，外连接语法 (*=) 并不总是给出正确的结果。有时它会将其解释为交叉联接而不是外部联接。因此，我们有充分的理由停止使用它。并且外连接语法是一个不推荐使用的功能，在 SQL Server 2008 之后的下一个版本的 SQL Server 中将不会出现。您仍然可以进行内连接，但为什么会有人想要呢？它们不清楚，而且更难维护。您不容易知道什么是连接的一部分，什么是真正的 where 子句。

One reason why I believe you should not use the old syntax is that understanding joins and what they do and do not do is a critical step for anyone who will write SQL code. You should not write any SQL code without understanding joins thoroughly. If you understand them well, you will probably come to the conclusion that the ANSI-92 syntax is clearer and easier to maintain. I've never met a SQL expert who didn't use the ANSI-92 syntax in preference to the old syntax.

我认为您不应该使用旧语法的一个原因是，对于任何将要编写 SQL 代码的人来说，了解连接以及它们做什么和不做什么是关键的一步。您不应该在没有彻底理解连接的情况下编写任何 SQL 代码。如果您很好地理解它们，您可能会得出结论，即 ANSI-92 语法更清晰且更易于维护。我从未遇到过不使用 ANSI-92 语法而不是旧语法的 SQL 专家。

Most people who I have met or dealt with who use the old code, truly don't understand joins and thus get into trouble when querying the database. This is my personal experience so I'm not saying it is always true. But as a data specialist, I've had to fix too much of this junk through the years not to believe it.

我遇到或处理过的大多数使用旧代码的人，确实不了解联接，因此在查询数据库时会遇到麻烦。这是我的个人经历，所以我并不是说它总是正确的。但作为一名数据专家，多年来我不得不修复太多这些垃圾，我不敢相信。

回答by Scot McDermid

I was taught ANSI-89 in school and worked in industry for a few years. Then I left the fabulous world of DBMS for 8 years. But then I came back and this new ANSI 92 stuff was being taught. I have learned the Join On syntax and now I actually teach SQL and I recommend the new JOIN ON syntax.

我在学校学习 ANSI-89，并在工业界工作了几年。然后我离开了 DBMS 的美妙世界 8 年。但后来我回来了，正在教授这个新的 ANSI 92 东西。我已经学习了 Join On 语法，现在我实际上在教 SQL，我推荐新的 JOIN ON 语法。

But the downside that I see is correlated subqueries don't seem to make sense in the light of ANSI 92 joins. When join information was included in the WHERE and correlated subqueries are "joined" in the WHERE all seemed right and consistent. In ANSI 92 table join criteria is not in the WHERE and subquery "join" is, the syntax seems inconsistent. On the other hand, trying to "fix" this inconsistency would probably just make it worse.

但我看到的缺点是，根据 ANSI 92 连接，相关子查询似乎没有意义。当连接信息包含在 WHERE 中并且相关子查询在 WHERE 中“连接”时，一切似乎都正确且一致。在 ANSI 92 表连接条件不在 WHERE 和子查询“join”是，语法似乎不一致。另一方面，试图“修复”这种不一致可能只会让情况变得更糟。

回答by Charles Bretana

I don't know the answer for sure.. this is a religous war (albiet of a lesser degree than Mac-Pc or others)

我不确定答案……这是一场宗教War（尽管程度低于 Mac-Pc 或其他）

A guess is that until fairly recently, Oracle, (and maybe other vendors as well) did not adopt the ANSI-92 standard (I think it was in Oracle v9, or thereabouts) and so, for DBAs/Db Developers working at companies which were still using these versions, (or wanted code to be portable across servers that might be using these versions, they had to stick to the old standard...

一种猜测是，直到最近，Oracle（可能还有其他供应商）还没有采用 ANSI-92 标准（我认为它是在 Oracle v9 中，或在其左右），因此，对于在以下公司工作的 DBA/Db 开发人员而言仍在使用这些版本，（或者希望代码可以在可能使用这些版本的服务器之间移植，他们必须坚持旧标准......

It's a shame really, because the new join syntax is much more readable, and the old syntax generates wrong (incorrect) results in several well-documented scenarios.

真的很遗憾，因为新的连接语法更具可读性，而旧的语法会在几个有据可查的场景中生成错误（不正确）的结果。

• Specifically, outer Joins when there are conditional filtering predicates on non-Join related columns from the table on the "outer" side of the join.

• 具体来说，当在连接的“外部”侧的表中的非连接相关列上存在条件过滤谓词时，外部连接。

回答by JPLemme

Inertia and practicality.

惯性和实用性。

ANSI-92 SQL is like touch-typing. In some theoretical way it might make everything better someday, but I can type much faster looking at the keys with four fingers now. I would need to go backwards in order to go forwards, with no guarantee that there would ever be a pay-off.

ANSI-92 SQL 就像触摸输入。从理论上讲，它可能有一天会让一切变得更好，但我现在可以用四根手指看着按键打字更快。我需要倒退才能前进，但不能保证会有回报。

Writing SQL is about 10% of my job. If I need ANSI-92 SQL to solve a problem that ANSI-89 SQL can't solve then I'll use it. (I use it in Access, in fact.) If using it all the time would help me solve my existing problems much faster, I'd spend the time to assimilate it. But I can whip out ANSI-89 SQL without ever thinking about the syntax. I get paid to solve problems--thinking about SQL syntax is a waste of my time and of my employer's money.

编写 SQL 约占我工作的 10%。如果我需要 ANSI-92 SQL 来解决 ANSI-89 SQL 无法解决的问题，那么我会使用它。（事实上​​，我在 Access 中使用它。）如果一直使用它可以帮助我更快地解决现有问题，我会花时间吸收它。但是我可以在不考虑语法的情况下抽出 ANSI-89 SQL。我因为解决问题而获得报酬——考虑 SQL 语法是在浪费我的时间和我雇主的钱。

Someday, young Grasshopper, you'll be defending your use of ANSI-92 SQL syntax against young people whining that you should be using SQL3 (or whatever). And then you'll understand. :-)

总有一天，年轻的 Grasshopper，您将为自己使用 ANSI-92 SQL 语法辩护，反对那些抱怨您应该使用 SQL3（或其他）的年轻人。然后你就会明白。:-)

回答by Dave

I had a query that was originally written for SQL Server 6.5, which did not support the SQL 92 join syntax, i.e.

我有一个最初是为 SQL Server 6.5 编写的查询，它不支持 SQL 92 连接语法，即

select foo.baz
from foo
  left outer join bar
  on foo.a = bar.a

was instead written as

而是写成

select foo.baz
from foo, bar
where foo.a *= bar.a

The query had been around for a while, and the relevant data had accumulated to make the query run too slow, abut 90 seconds to complete. By the time this problem arose, we had upgraded to SQL Server 7.

查询已经有一段时间了，相关数据已经积累，导致查询运行速度太慢，几乎需要 90 秒才能完成。当这个问题出现时，我们已经升级到 SQL Server 7。

After mucking about with indexes and other Easter-egging, I changed the join syntax to be SQL 92 compliant. The query time dropped to 3 seconds.

在考虑了索引和其他复活节彩蛋之后，我将连接语法更改为符合 SQL 92。查询时间下降到 3 秒。

There's a good reason to switch.

有充分的理由转换。

Reposted from here.

从这里转贴。

回答by magallanes

1) Standard way to write OUTER JOIN, versus *= or (+)=

1) 写 OUTER JOIN 的标准方法，与 *= 或 (+)=

2) NATURAL JOIN

2) 自然连接

3) Depend in the database engine, ANSI-92 trends to be more optimal.

3) 依赖于数据库引擎，ANSI-92 趋向于更加优化。

4) Manual optimization :

4）手动优化：

Let's say that we have the next syntax (ANSI-89):

假设我们有下一个语法 (ANSI-89)：

(1)select * from TABLE_OFFICES to,BIG_TABLE_USERS btu
where to.iduser=tbu.iduser and to.idoffice=1

It could be written as:

可以写成：

(2)select * from TABLE_OFFICES to
inner join BIG_TABLE_USERS btu on to.iduser=tbu.iduser
where to.idoffice=1

But also as :

但也作为：

(3)select * from TABLE_OFFICES to
inner join BIG_TABLE_USERS btu on to.iduser=tbu.iduser and to.idoffice=1

All of them (1),(2),(3) return the same result, however they are optimized differently, it depends in the database engine but most of them do :

它们 (1),(2),(3) 都返回相同的结果，但是它们的优化方式不同，这取决于数据库引擎，但大多数都这样做：

• (1) its up to the database engine decide the optimization.
• (2) it joins both tables then do the filter per office.
• (3) it filters the BIG_TABLE_USERS using the idoffice then join both tables.

• (1) 由数据库引擎决定优化。
• (2) 它连接两个表，然后对每个办公室进行过滤。
• (3) 它使用 idoffice 过滤 BIG_TABLE_USERS 然后加入两个表。

5) Longer queries are less messy.

5）较长的查询不那么混乱。