Is Left Join Commutative? What Are Its Properties?

Assume tables TableA TableB TableC and TableD: Is the following query: TableA INNER JOIN TableB LEFT JOIN TableC LEFT JOIN TableD (all joined to an id column) equivalent to: Ta

Solution 1:

Wikipedia:

"In mathematics, a binary operation is commutative if changing the order of the operands does not change the result. It is a fundamental property of many binary operations, and many mathematical proofs depend on it."

Answer:

no, a left join is not commutative. And inner join is.

But that's not really what you are asking.

Is the following query:

TableA INNERJOIN TableB LEFTJOIN TableC LEFTJOIN TableD

(all joined to an id column) equivalent to:

TableA INNERJOIN TableB
       INNERJOIN TableC
        LEFTJOIN TableD   
UNION     
TableA INNERJOIN TableB
        LEFTJOIN TableC ON TableB.c_id ISNULLLEFTJOIN TableD    

Answer:

Also no. Unions and joins don't really accomplish the same thing, generally speaking. In some case you may be able to write them equivalently, but I don't think so general pseudo sql you are showing. The ON constitution seemslike it should not work (maybe something about which I do not know in MySQL?)

Here is a simplified set of queries that I do think would be equivalent.

SELECT*FROM TableA a 
       LEFTJOIN 
       TableB b ON a.id = b.id_a 

SELECT*FROM TableA a 
       INNERJOIN 
       TableB b ON a.id = b.id_a 
UNIONSELECT*FROM TableA a  
       LEFTJOIN 
       TableB b ON a.id = b.id_a 
 WHERE TableB.id ISNULL

Edit 2:

Here's another example that is closer to your but in essence the same.

SELECT*FROM            TableA a 
       INNERJOIN TableB b ON a.id = b.id_a 
        LEFTJOIN TableC c ON b.id = c.id_b 

is the same as

SELECT*FROM TableA a 
       INNERJOIN TableB b ON a.id = b.id_a 
       INNERJOIN TableC c ON b.id = c.id_b 
UNIONSELECT*FROM TableA a  
       INNERJOIN TableB b ON a.id = b.id_a 
        LEFTJOIN TableC c ON b.id = c.id_b 
 WHERE TableC.id ISNULL

But I still don't think I'm answering your real question.