左外部联接是关联的吗? [英] 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 outer join B) left outer join C不应等于A left outer 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屋！