oracle 为什么使用 JOIN 子句而不是 WHERE 条件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1613304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why use a JOIN clause versus a WHERE condition?
提问by Jay
I develop against Oracle databases. When I need to manually write (not use an ORM like hibernate), I use a WHERE condition instead of a JOIN.
我是针对 Oracle 数据库开发的。当我需要手动编写（而不是像休眠一样使用 ORM）时，我使用 WHERE 条件而不是 JOIN。
for example (this is simplistic just to illustrate the style):
例如（这只是为了说明风格而简单化）：
Select *
from customers c, invoices i, shipment_info si
where c.customer_id = i.customer_id
and i.amount > 999.99
and i.invoice_id = si.invoice_id(+) -- added to show a replacement for a join
order by i.amount, c.name
I learned this style from an OLD oracle DBA. I have since learned that this is not standard SQL syntax. Other than being non-standard and much less database portable, are there any other repercussions to using this format?
我从一个老 Oracle DBA 那里学到了这种风格。我后来了解到这不是标准的 SQL 语法。除了非标准和数据库可移植性低得多之外，使用这种格式还有其他影响吗？
采纳答案by Erich Kitzmueller
Some people will say that this style is less readable, but that's a matter of habit. From a performance point of view, it doesn't matter, since the query optimizer takes care of that.
有人会说这种风格可读性较差，但那是习惯问题。从性能的角度来看，这并不重要，因为查询优化器会负责。
回答by Hank Gay
I don't like the style because it makes it harder to determine which WHEREclauses are for simulating JOINsand which ones are for actual filters, and I don't like code that makes it unnecessarily difficult to determine the original intent of the programmer.
我不喜欢这种风格，因为它使确定哪些WHERE子句用于模拟JOINs以及哪些子句用于实际过滤器变得更加困难，而且我不喜欢使确定程序员的原始意图变得不必要的困难的代码。
回答by Adam Paynter
The biggest issue that I have run into with this format is the tendency to forget some join's WHEREclause, thereby resulting in a cartesian product. This is particularly common (for me, at least) when adding a new table to the query. For example, suppose an ADDRESSEStable is thrown into the mix and your mind is a bit forgetful:
我在这种格式中遇到的最大问题是容易忘记某些连接WHERE子句，从而导致笛卡尔积。在向查询添加新表时，这尤其常见（至少对我而言）。例如，假设一张ADDRESSES桌子被扔进了混合物中，你的头脑有点健忘：
SELECT *
FROM customers c, invoices i, addresses a
WHERE c.customer_id = i.customer_id
AND i.amount > 999.99
ORDER BY i.amount, c.name
Boom! Cartesian product! :)
繁荣！笛卡尔积！:)
回答by Charles Bretana
The old style join is flat out wrong in some cases (outer joins are the culprit). Although they are more or less equivalent when using inner joins, they can generate incorrect results with outer joins, especially if columns on the outer side can be null. This is because when using the older syntax the join conditions are not logicallyevaluated until the entire result set has been constructed, it is simply not possible to express a condition on a column from outer side of a join that will filter records when the column can be null because there is no matching record.
在某些情况下，旧式连接完全错误（外部连接是罪魁祸首）。尽管在使用内连接时它们或多或少是等效的，但它们可能会使用外连接生成不正确的结果，尤其是在外侧的列可以为空的情况下。这是因为当使用旧语法时，连接条件在构建整个结果集之前不会进行逻辑评估，根本不可能从连接的外侧表达对列的条件，当列可以过滤记录时为空，因为没有匹配的记录。
As an example:
举个例子：
Select allCustomers, and the sum of the sales of Widgets on all their Invoices in the month Of August, where the Invoice has been processed (Invoice.ProcessDate is NotNull)
选择所有客户，以及发票已处理的八月份所有发票上小部件的销售额总和（Invoice.ProcessDate不为空）
using new ANSI-92 Join syntax
使用新的 ANSI-92 连接语法
Select c.name, Sum(d.Amount)
From customer c
Left Join Invoice I
On i.custId = c.custId
And i.SalesDate Between '8/1/2009'
and '8/31/2009 23:59:59'
And i.ProcessDate Is Not Null
Left Join InvoiceDetails d
On d.InvoiceId = i.InvoiceId
And d.Product = 'widget'
Group By c.Name
Try doing this with old syntax... Because when using the old style syntax, all the conditions in the where clause are evaluated/applied BEFORE the 'outer' rows are added back in, All the UnProcessed Invoice rows will get added back into the final result set... So this is not possible with old syntax - anything that attempts to filter out the invoices with null Processed Dates will eliminate customers... the only alternative is to use a correlated subquery.
尝试使用旧语法执行此操作...因为在使用旧样式语法时，where 子句中的所有条件都会在重新添加“外部”行之前进行评估/应用，所有未处理的发票行都将重新添加到最终结果集......因此，使用旧语法这是不可能的 - 任何试图过滤掉处理日期为空的发票的事情都会消除客户......唯一的选择是使用相关的子查询。
回答by Joel Coehoorn
I have since learned that this is not standard SQL syntax.
我后来了解到这不是标准的 SQL 语法。
That's not quite true. The "a,b where"syntax is from the ansi-89 standard, the "a join b on"syntax is ansi-92. However, the 89 syntax is deprecated, which means you should notuse it for new queries.
这并不完全正确。该"a,b where"语法与ANSI-89标准中，"a join b on"语法是ANSI-92。但是，89语法弃用，这意味着你应该不会将其用于新的查询。
Also, there are some situations where the older style lacks expressive power, especially with regard to outer joins or complex queries.
此外，在某些情况下，旧样式缺乏表达能力，尤其是在外连接或复杂查询方面。
It can be a pain going through the where clause trying to pick out join conditions. For anything more than one join the old style is absolute evil. And once you know the new style, you may as well just keep using it.
通过 where 子句尝试挑选连接条件可能会很痛苦。对于任何不止一个加入的旧风格是绝对邪恶的。一旦你知道了新的风格，你不妨继续使用它。
回答by ercan
It really depends on habits, but I have always found Oracle's comma separated syntax more natural. The first reason is that I think using (INNER) JOINdiminishes readability. The second is about flexibility. In the end, a join is a cartesian product by definition. You do not necessarily have to restrict the results based on IDs of both tables. Although very seldom, one might well need cartesian product of two tables. Restricting them based on IDs is just a very reasonable practice, but NOT A RULE. However, if you use JOINkeyword in e.g. SQL Server, it won't let you omit the ONkeyword. Suppose you want to create a combination list. You have to do like this:
这真的取决于习惯，但我一直发现 Oracle 的逗号分隔语法更自然。第一个原因是我认为使用(INNER) JOIN 会降低可读性。二是关于灵活性。最后，根据定义，连接是笛卡尔积。您不必根据两个表的 ID 限制结果。虽然很少，但很可能需要两张表的笛卡尔积。根据 ID 限制它们只是一种非常合理的做法，但不是规则。但是，如果您在例如 SQL Server 中使用JOIN关键字，它不会让您省略ON关键字。假设您要创建一个组合列表。你必须这样做：
SELECT *
FROM numbers
JOIN letters
ON 1=1
Apart from that, I find the (+)syntax of Oracle also very reasonable. It is a nice way to say, "Add this record to the resultset too, even if it is null." It is way better than the RIGHT/LEFT JOINsyntax, because in fact there is no left or right! When you want to join 10 tables with several different types of outer joins, it gets confusing which table is on the "left hand side" and which one on the right.
除此之外，我发现Oracle的(+)语法也非常合理。说“将此记录也添加到结果集中，即使它为空”是一种很好的方式。它比RIGHT/LEFT JOIN语法要好得多，因为实际上没有左或右！当您想用几种不同类型的外连接连接 10 个表时，会混淆哪个表在“左侧”，哪个表在右侧。
By the way, as a more general comment, I don't think SQL portability exists in the practical world any more. The standard SQL is so poor and the expressiveness of diverse DBMS specific syntax are so often demanded, I don't think 100% portable SQL code is an achievable goal. The most obvious evidence of my observation is the good old row numberproblemmatic. Just search any forum for "sql row number", including SO, and you will see hundreds of posts asking how it can be achieved in a specific DBMS. Similar and related to that, so is limiting the number of returned rows, for example..
顺便说一句，作为更一般的评论，我认为 SQL 可移植性不再存在于实际世界中。标准 SQL 太差，而且经常要求不同 DBMS 特定语法的表达能力，我认为 100% 可移植的 SQL 代码不是一个可以实现的目标。我观察的最明显证据是有问题的旧行号。只需在任何论坛上搜索“ sql row number”，包括 SO，您就会看到数百篇帖子询问如何在特定的 DBMS 中实现它。与此类似并与之相关，例如限制返回的行数也是如此。
回答by Larry Lustig
This is a standard SQL syntax, just an olderstandard than JOIN. There's a reason that the syntax has evolved and you should use the newer JOIN syntax because:
这是一个标准的 SQL 语法，只是一个比 JOIN更旧的标准。语法已经发展是有原因的，您应该使用较新的 JOIN 语法，因为：
It's more expressive, clearly indicating which tables are JOINed, the JOIN order, which conditions apply to which JOIN, and separating out the filtering WHERE conditions from the JOIN conditions.
It supports LEFT, RIGHT, and FULL OUTER JOINs, which the WHERE syntax does not.
它更具表现力，清楚地指示了哪些表是 JOIN 的，JOIN 顺序，哪些条件适用于哪个 JOIN，并将过滤 WHERE 条件与 JOIN 条件分开。
它支持 LEFT、RIGHT 和 FULL OUTER JOIN，而 WHERE 语法不支持。
I don't think you'll find the WHERE-type JOIN substantially less portable than the JOIN syntax.
我认为您不会发现 WHERE 类型的 JOIN 比 JOIN 语法的可移植性低得多。
回答by Rob van Laarhoven
As long as you don't use the ANSI natural join feature I'm OK with it.
只要您不使用 ANSI 自然连接功能，我就可以接受。
I found this quote by – ScottCher, I totally agree:
我找到了 - ScottCher 的这句话，我完全同意：
I find the WHERE syntax easier to read than INNER JOIN - I guess its like Vegemite. Most people in the world probably find it disgusting but kids brought up eating it love it.
我发现 WHERE 语法比 INNER JOIN 更容易阅读——我猜它就像 Vegemite。世界上大多数人可能会觉得它很恶心，但从小就喜欢吃它的孩子们喜欢它。
回答by DVK
This is Transact SQL syntax, and I'm not quite sure how "unportable" it is - it is the main syntax used in Sybase, for example (Sybase supports ANSI syntax as well) as well as many other databases (if not all).
这是 Transact SQL 语法，我不太确定它有多“不可移植”——例如，它是 Sybase 中使用的主要语法（Sybase 也支持 ANSI 语法）以及许多其他数据库（如果不是全部） .
The main benefits to ANSI syntax is that it allows you to write some fairly tricky chained joins that T-SQL prohibits
ANSI 语法的主要好处是它允许您编写一些 T-SQL 禁止的相当棘手的链式连接
回答by MusiGenesis
Actually, this syntax is moreportable than a JOIN, because it will work with pretty much any database, whereas not everybody supports the JOIN syntax (Oracle Lite doesn't, for example [unless this has changed recently]).
实际上，这种语法比 JOIN更具可移植性，因为它几乎适用于任何数据库，但并非所有人都支持 JOIN 语法（例如，Oracle Lite 不支持 [除非最近发生了变化]）。
