Are left outer joins associative?
join, outer-join, sql
Solution
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.
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.
1. One of the JOIN conditions involves columns from all 3 tables
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.
2. One of the JOIN conditions can be satisfied despite all fields from one table being 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;
You can see this in action here: http://sqlfiddle.com/#!9/d59139/1
Problem
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. Suppose we have three tables: A, B, and C. 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. 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. Let C contain two columns, ID and VALUE, where ID is the primary key of table C and VALUE just contains some arbitrary values. Then shouldn't `(A left outer join B) left outer join C` be equal to `A left outer join (B left outer join C)`?