PL/SQL Using multiple left join

Question

SELECT * FROM Table A LEFT JOIN TABLE B LEFT JOIN TABLE C

From the snippet above, TABLE C will left join into (TABLE B) or (data from TABLE A LEFT JOIN TABLE B) or (TABLE A)?

Answer 1

TABLE C will left join into 1. (TABLE B) or 2. (data from TABLE A LEFT JOIN TABLE B) or 3. (TABLE A)?

The second. But The join condition will help you to understand more.

You can write:

SELECT * 
FROM Table A 
LEFT JOIN TABLE B ON (A.id = B.id)
LEFT JOIN TABLE C ON (A.ID = C.ID)

But you are able to:

SELECT * 
FROM Table A 
LEFT JOIN TABLE B ON (A.id = B.id)
LEFT JOIN TABLE C ON (A.id = C.id and B.code = C.code)

So, you can join on every field from previous tables and you join on "the result" (though the engine may choose its way to get the result) of the previous joins.

Think at left join as non-commutative operation (A left join B is not the same as B left join A) So, the order is important and C will be left joined at the previous joined tables.

Answer 2

The Oracle documentation is quite specific about how the joins are processed:

To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result.

This is the logic approach to handling the joins and is consistent with the ANSI standard (in other words, all database engines process the joins in order).

However, when the query is actually executed, the optimizer may choose to run the joins in a different order. The result needs to be logically the same as processing the joins in the order given in the query.

Also, the join conditions may cause some unexpected conditions to arise. So if you have:

from A left outer join
     B
     on A.id = B.id left outer join
     C
     on B.id = C.id

Then, you might have the condition where A and C each have a row with a particular id, but B does not. With this formulation, you will not see the row in C because it is joining to NULL. So, be careful with join conditions on left outer join, particularly when joining to a table other than the first table in the chain.