sql - Are left outer joins associative?

Question

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)?

Answer 1

In this thread, it is said, that they are not associative: Is LEFT OUTER JOIN associative?

However, I've found some book online where it is stated, that OUTER JOINs are associative, when the tables on the far left side and far right side have no attributes in common (here).

Here is a graphical presentation (MSPaint ftw):

Another way to look at it:

Since you said that table A joins with B, and B joins with C, then:

• When you first join A and B, you are left with all records from A. Some of them have values from B. Now, for some of those rows for which you got value from B, you get values from C.
• When you first join B and C, you and up with the whole table B, where some of the records have values from C. Now, you take all records from A and join some of them with all rows from B joined with C. Here, again, you get all rows from A, but some of them have values from B, some of which have values from C.

I don't see any possibility where, in conditons described by you, there would be a data loss depending on the sequence of LEFT joins.

Basing on the data provided by Tilak in his answer (which is now deleted), I've built a simple test case:

CREATE TABLE atab (id NUMBER, val VARCHAR2(10));
CREATE TABLE btab (id NUMBER, val VARCHAR2(10));
CREATE TABLE ctab (id NUMBER, val VARCHAR2(10));

INSERT INTO atab VALUES (1, 'A1');
INSERT INTO atab VALUES (2, 'A2');
INSERT INTO atab VALUES (3, 'A3');

INSERT INTO btab VALUES (1, 'B1');
INSERT INTO btab VALUES (2, 'B2');
INSERT INTO btab VALUES (4, 'B4');

INSERT INTO ctab VALUES (1, 'C1');
INSERT INTO ctab VALUES (3, 'C3');
INSERT INTO ctab VALUES (5, 'C5');

SELECT ab.aid, ab.aval, ab.bval, c.val AS cval
  FROM (
    SELECT a.id AS aid, a.val AS aval, b.id AS bid, b.val AS bval
      FROM atab a LEFT OUTER JOIN btab b ON (a.id = b.id)
    ) ab
    LEFT OUTER JOIN ctab c ON (ab.bid = c.id)
ORDER BY ab.aid
;

       AID AVAL       BVAL       CVAL     
---------- ---------- ---------- ----------
         1 A1         B1         C1         
         2 A2         B2                    
         3 A3                               

SELECT a.id, a.val AS aval, bc.bval, bc.cval
  FROM
    atab a
    LEFT OUTER JOIN (
      SELECT b.id AS bid, b.val AS bval, c.id AS cid, c.val AS cval
        FROM btab b LEFT OUTER JOIN ctab c ON (b.id = c.id)
    ) bc
      ON (a.id = bc.bid)
ORDER BY a.id
;

        ID AVAL       BVAL       CVAL     
---------- ---------- ---------- ----------
         1 A1         B1         C1         
         2 A2         B2                    
         3 A3                            

It seems in this particular example, that both solutions give the same result. I can't think of any other dataset that would make those queries return different results.

Check at SQLFiddle:

• MySQL
• Oracle
• PostgreSQL
• SQLServer