Are Left Outer Joins Associative

Are left outer joins associative?

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

Does the order of tables in a join matter, when LEFT (outer) joins are used?

It is the same but it is ambiguous as hell with the implicit CROSS JOINs. Use explicit JOINS.

If you are joining in the WHERE clause then the results may differ because joins and filters are mixed up.

SELECT ....
  FROM apples a
       JOIN
       bananas b ON ...
       JOIN 
       oranges o ON ...
       LEFT JOIN
       kiwis k ON k.orange_id = o.id
 WHERE (filters only)

Notes:

• INNER JOINS and CROSS JOINS are commutative and associative: order does not matter usually.
• OUTER JOINS are not, which you identified
• SQL is declarative: you tell the optimiser what you want, not how to do it. This removes JOIN order considerations (subject to the previous 2 items)

What is the difference between INNER JOIN and OUTER JOIN?

Assuming you're joining on columns with no duplicates, which is a very common case:

• An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.

• An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Left Join on Associative Table

Then this will work for you... Prejoin a Cartesian against all prospects and elements within that project via a select as your first FROM table. Then, left join to the conjoinprospect. You can obviously change / eliminate certain columns from result, but at least all is there, in the join you want with exact results you are expecting...

SELECT 
       PJ.*,
       CJP.Value
    FROM 
       (   SELECT 
                   P.ID ProspectID,
                   P.Name,
                   P.ProjectID,
                   CJ.Title,
                   CJ.ID ConJointID
               FROM 
                   Prospect P,
                   ConJoint CJ
               where 
                       P.ProjectID = 1
                   AND P.ProjectID = CJ.ProjectID
               ORDER BY 
                   1, 4
         ) PJ
         LEFT JOIN conjointProspect cjp 
             ON PJ.ProspectID = cjp.prospectID 
            AND PJ.ConjointID = cjp.conjointid
     ORDER BY 
        PJ.ProspectID,
        PJ.ConJointID

SQL joining three tables, join precedence

All kinds of outer and normal joins are in the same precedence class and operators take effect left-to-right at a given nesting level of the query. You can put the join expression on the right side in parentheses to cause it to take effect first. Remember that you will have to move the ON clauses around so that they stay with their joins—the join in parentheses takes its ON clause with it into the parentheses, so it now comes textually before the other ON clause which will be after the parentheses in the outer join statement.

(PostgreSQL example)

In

SELECT * FROM a LEFT JOIN b ON (a.id = b.id) JOIN c ON (b.ref = c.id);

the a-b join takes effect first, but we can force the b-c join to take effect first by putting it in parentheses, which looks like:

SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

Often you can express the same thing without extra parentheses by moving the joins around and changing the direction of the outer joins, e.g.

SELECT * FROM b JOIN c ON (b.ref = c.id) RIGHT JOIN a ON (a.id = b.id);

---

Related Topics