左外连接是关联的吗? [英] Are left outer joins associative?

问题描述

很容易理解为什么左外连接不是可交换的，但我在理解它们是否关联时遇到了一些麻烦.一些在线资源表明他们不是，但我没能说服自己这是事实.

It's easy to understand why left outer joins are not commutative, but I'm having some trouble understanding whether they are associative. Several online sources suggest that they are not, but I haven't managed to convince myself that this is the case.

假设我们有三个表:A、B 和 C.

Suppose we have three tables: A, B, and C.

设A包含两列，ID和B_ID，其中ID是表A的主键，B_ID是表B的主键对应的外键.

Let A contain two columns, ID and B_ID, where ID is the primary key of table A and B_ID is a foreign key corresponding to the primary key of table B.

设B包含两列，ID和C_ID，其中ID是表B的主键，C_ID是表C的主键对应的外键.

Let B contain two columns, ID and C_ID, where ID is the primary key of table B and C_ID is a foreign key corresponding to the primary key of table C.

让 C 包含两列，ID 和 VALUE，其中 ID 是表 C 的主键，VALUE 只包含一些任意值.

Let C contain two columns, ID and VALUE, where ID is the primary key of table C and VALUE just contains some arbitrary values.

那么不应该(A left external join B) left outer join C等于A left external join (B left outer join C)吗?

推荐答案

如果你假设你在外键上加入，正如你的问题所暗示的那样，那么是的，我认为 OUTER JOIN 肯定是关联，如Przemyslaw Kruglej 的回答所述.

If you're assuming that you're JOINing on a foreign key, as your question seems to imply, then yes, I think OUTER JOIN is guaranteed to be associative, as covered by Przemyslaw Kruglej's answer.

然而，鉴于您实际上还没有指定 JOIN 条件，迂腐的正确答案是不，它们不能保证是关联的.有两种简单的方法可以使用反常的 ON 子句破坏结合性.

However, given that you haven't actually specified the JOIN condition, the pedantically correct answer is that no, they're not guaranteed to be associative. There are two easy ways to violate associativity with perverse ON clauses.

这是违反关联性的一种非常便宜的方法，但严格来说，您的问题中没有任何内容禁止它.使用问题中建议的列名称，考虑以下两个查询:

This is a pretty cheap way to violate associativity, but strictly speaking nothing in your question forbade it. Using the column names suggested in your question, consider the following two queries:

-- This is legal
SELECT * FROM (A JOIN B ON A.b_id = B.id) 
              JOIN C ON (A.id = B.id) AND (B.id = C.id)


-- This is not legal
SELECT * FROM A
              JOIN (B JOIN C ON (A.id = B.id) AND (B.id = C.id))
              ON A.b_id = B.id

底部查询甚至不是有效查询，但顶部查询是.显然，这违反了结合性.

The bottom query isn't even a valid query, but the top one is. Clearly this violates associativity.

这样，我们甚至可以根据 JOIN 的顺序在结果集中有不同数量的行.例如，设在 B 上加入 A 的条件是 A.b_id = B.id，但在 C 上加入 B 的条件是 B.id IS NULL.

This way, we can even have different numbers of rows in our result set depending upon the order of the JOINs. For example, let the condition for JOINing A on B be A.b_id = B.id, but the condition for JOINing B on C be B.id IS NULL.

因此我们得到这两个查询，输出非常不同:

Thus we get these two queries, with very different output:

SELECT * FROM (A LEFT OUTER JOIN B ON A.b_id = B.id) 
              LEFT OUTER JOIN C ON B.id IS NULL;


SELECT * FROM A 
              LEFT OUTER JOIN (B LEFT OUTER JOIN C ON B.id IS NULL)
              ON A.b_id = B.id;

你可以在这里看到这个:http://sqlfiddle.com/#!9/d59139/1

You can see this in action here: http://sqlfiddle.com/#!9/d59139/1

这篇关于左外连接是关联的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！