为什么 SQL ANSI-92 标准没有比 ANSI-89 更好地采用? [英] Why isn't SQL ANSI-92 standard better adopted over ANSI-89?

问题描述

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

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

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

而不是 ANSI-92 标准:

rather than the ANSI-92 standard:

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

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

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.

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

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?

推荐答案

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

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.

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

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-89 习惯如此困难，我只能假设有一大群金字塔基部"的程序员通过复制和代码编写代码.粘贴，使用书籍、杂志文章或其他代码库中的古老示例，这些人不会抽象地学习新语法.有些人模式匹配，有些人死记硬背.

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-92 语法.自 1994 年以来，我一直在网上回答 SQL 问题.

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 ANSI-92 标准没有比 ANSI-89 更好地采用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！