哪种连接语法更好? [英] Which join syntax is better?

问题描述

因此，我们正在从Informix迁移到Sql Server.而且我注意到在Informix中，查询是以这种方式编写的:

So we are migrating from Informix to Sql Server. And I have noticed that in Informix the queries are written in this manner:

select [col1],[col2],[col3],[col4],[col5]
from tableA, tableB
where tableA.[col1] = table.[gustavs_custom_chrome_id]

我在SQL Server中编写的所有查询都写为:

Whereas all the queries I write in SQL Server are written as:

select [col1],[col2],[col3],[col4],[col5]
from tableA 
inner join tableB on tableA.[col1] = table.[gustavs_custom_chrome_id]

现在，我的第一个想法是:第一个查询是错误的.它可能创建了这个庞大的记录集，然后使用Where子句缩小到实际的记录集.因此，这对性能不利.这不是安西.所以这是双重糟糕.

Now, my first thought was: that first query is bad. It probably creates this huge record set then whittles to the actual record set using the Where clause. Therefore, it's bad for performance. And it's non-ansi. So it's double bad.

但是，经过一番谷歌搜索后，从理论上讲，它们似乎几乎是相同的.而且它们都符合ANSI.

However, after some googling, it seems that they both are, in theory, pretty much the same. And they both are ANSI compliant.

所以我的问题是:

1. 两个查询执行相同吗? IE.运行速度一样快，并且总是给出相同的答案.
2. 两者都真正符合ANSI标准吗?
3. 我有什么突出的理由要追求一种风格而不是另一种风格?还是应该让我一个人呆得足够好?
   
   
   注意:这些只是查询的示例.我已经看到一些查询(第一种查询)一次最多可连接5个表.

1. Do both queries perform the same? IE. runs just as fast and always gives the same answer.
2. Are both really ANSI-compliant?
3. Are there any outstanding reasons why I should push for one style over another? Or should I just leave good enough alone?
   
   
   Note: These are just examples of the queries. I've seen some queries (of the first kind) join up to 5 tables at a time.

推荐答案

好吧，更好"是主观的.这里有一些风格.但我会直接解决您的问题.

Well, "better" is subjective. There is some style here. But I'll address your questions directly.

1. 两者执行相同的操作
2. 两者均符合ANSI.

3. 第一个示例的问题是

1. Both perform the same
2. Both are ANSI-compliant.

3. The problem with the first example is that

• 很容易无意间得出叉积(因为更容易省略连接条件)

• it is very easy to inadvertently derive the cross product (since it is easier to leave out join criteria)

随着向联接中添加越来越多的表，调试联接条件也变得困难

it also becomes difficult to debug the join criteria as you add more and more tables to the join

，因为旧式外部联接(* =)语法已被弃用(

since the old-style outer join (*=) syntax has been deprecated (it has long been documented to return incorrect results), when you need to introduce outer joins, you need to mix new style and old style joins ... why promote inconsistency?

虽然它并不是最佳实践的权威，但 Microsoft建议使用显式的INNER/OUTER JOIN语法

while it's not exactly the authority on best practices, Microsoft recommends explicit INNER/OUTER JOIN syntax

使用后一种方法:

• 无论内部/外部，您都在使用一致的连接语法
• 意外得出叉积会更困难(并非不可能)
• 将联接条件与过滤条件隔离开可以使调试更加容易

我写了凯文(Kevin)指出.

这篇关于哪种连接语法更好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！