为什么没有比ANSI-89更好地采用SQL ANSI-92标准? [英] 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-92而不是ANSI-89有什么具体的性能优势?我会自己尝试，但是我们在这里使用的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语法.它获得批准已经十六年了，现在该是人们开始使用它的时候了！现在，所有品牌的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 abhorrent (+) Oracle syntax or *= Microsoft/Sybase syntax.

关于为什么很难打破开发人员社区的SQL-89习惯，我只能假设存在一个很大的金字塔基础"，这些程序员通过复制& amp;代码来编写代码.使用书籍，杂志文章或其他代码库中的古老示例进行粘贴，这些人不会抽象地学习新语法.有些人会进行模式匹配，有些人会死记硬背.

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.

这篇关于为什么没有比ANSI-89更好地采用SQL ANSI-92标准?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！