Is left join commutative? What are its properties?

Question

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:

TableA INNER JOIN TableB
     INNER JOIN TableC
     LEFT JOIN TableD    

UNION  

TableA INNER JOIN TableB
    LEFT JOIN TableC ON TableB.c_id IS NULL
    LEFT JOIN TableD

?

Note:
Or instead of union just do

TableA INNER JOIN TableB
       INNER JOIN TableC
        LEFT JOIN TableD

And then

TableA INNER JOIN TableB
       LEFT JOIN TableC ON TableB.c_id IS NULL
       LEFT JOIN TableD

and then combine the results

Update
Is

(A INNER JOIN B) LEFT JOIN C LEFT JOIN D

the same as:

A INNER JOIN (B LEFT JOIN C) LEFT JOIN D

?

@HamletHakobyan:Because I am not sure if the sql optimizer changes the order of tables and as a result the query changes — Jim, Apr 14 '14 at 19:32
**`LEFT`** `JOIN` means that it keeps (at least) every row from the table on the left, so no, it isn't "commutative" — Lamak, Apr 14 '14 at 19:33
I don't see the order changes in provided example. If your question is `TableA LEFT JOIN TableB <=> TableB LEFT JOIN TableA`, the answer is NO. — Hamlet Hakobyan, Apr 14 '14 at 19:35
@HamletHakobyan:Also does the second query do: `Previous results LEFT JOIN TableD`? — Jim, Apr 14 '14 at 19:38
When you ask question you have some context in your mind, but it is ONLY IN YOUR mind. — Hamlet Hakobyan, Apr 14 '14 at 19:42
@Jim . . . The semantics of the query (what it does) are determined by the ordering of the tables in the `from` clause and the types of joins used to connect them. The SQL optimizer may choose a different execution order, but the results of a given query are well defined based on the SQL statement. — Gordon Linoff, Apr 14 '14 at 19:47
In a Venn diagram, with A overlapping B, a query where A is left-joined to B is the bit of the diagram which is only A, plus the bit of the diagram which is both A and B. For the bits which is only A, the B values are `NULL`. — halfer, Apr 14 '14 at 19:49
@halfer:Basically I am trying to break the original query to an inner + outer join without changing the results — Jim, Apr 14 '14 at 19:55
Ah right, I thought this might just be a theoretical question. So, I think the two are equivalent, yes: before the union you have the overlap of C with the tables before it, and after the union you have the rows of C that are not in that overlap. Test it with real data, of course. — halfer, Apr 14 '14 at 19:58
(Btw, if your purpose is just optimisation, then it may be better to post a question containing the whole query, an explain plan and your indexes. In that case, sub-selects in the column signature or in the tables list can sometimes net improvements). — halfer, Apr 14 '14 at 20:00
@halfer:This is actually my problem and I decided to do it by breaking up the query.http://stackoverflow.com/questions/23062134/optimal-way-to-fill-in-missing-values-after-a-left-join — Jim, Apr 14 '14 at 20:05
Seems like someone else is in the middle of helping you! Do reply to them... `:)`. — halfer, Apr 14 '14 at 20:13

Karl Kieninger · Answer 1 · 2014-04-14T21:22:52.853

0

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 INNER JOIN TableB LEFT JOIN TableC LEFT JOIN TableD

(all joined to an id column) equivalent to:

TableA INNER JOIN TableB
       INNER JOIN TableC
        LEFT JOIN TableD   
UNION     
TableA INNER JOIN TableB
        LEFT JOIN TableC ON TableB.c_id IS NULL
        LEFT JOIN 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 
       LEFT JOIN 
       TableB b ON a.id = b.id_a 

SELECT * 
  FROM TableA a 
       INNER JOIN 
       TableB b ON a.id = b.id_a 
UNION      
SELECT * 
  FROM TableA a  
       LEFT JOIN 
       TableB b ON a.id = b.id_a 
 WHERE TableB.id IS NULL

Edit 2:

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

SELECT * 
  FROM            TableA a 
       INNER JOIN TableB b ON a.id = b.id_a 
        LEFT JOIN TableC c ON b.id = c.id_b

is the same as

SELECT * 
  FROM TableA a 
       INNER JOIN TableB b ON a.id = b.id_a 
       INNER JOIN TableC c ON b.id = c.id_b 
UNION      
SELECT * 
  FROM TableA a  
       INNER JOIN TableB b ON a.id = b.id_a 
        LEFT JOIN TableC c ON b.id = c.id_b 
 WHERE TableC.id IS NULL

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

edited Apr 14 '14 at 21:22

answered Apr 14 '14 at 20:09

Karl Kieninger

8,841
2
33
49

Karl, can you outline why you don't think they are equivalent? I think they are, for the reasons I outlined in my comment. – halfer Apr 14 '14 at 20:12
So the combination of the results of `TableA INNER JOIN TableB INNER JOIN TableC LEFT JOIN TableD` and `TableA INNER JOIN TableB LEFT JOIN TableC ON TableB.c_id IS NULL LEFT JOIN TableD` is not the same result set as the original query? – Jim Apr 14 '14 at 20:26
Where is `TableC` and `TableD` in your query? The left join is on TableC and not TableB – Jim Apr 14 '14 at 20:38
I don't think it will be. Like my update says, i think if you move that from the on to there where it would be. Proof is in the pudding, though, so put a demo in sqlfiddle.com and see if it works. Certainly I maybe confused by the incomplete sql in the examples. – Karl Kieninger Apr 14 '14 at 20:40
Jim, I simplified in my example. The extra tables don't matter. – Karl Kieninger Apr 14 '14 at 20:40
But I do a left join on tableC because there might be missing rows in tableC – Jim Apr 14 '14 at 20:47
You are doing left join of tableA to tableB. Now I am cofused – Jim Apr 14 '14 at 21:00
It's a simplified example. You don't need the extra tables and joins to talk about the issue. Tables a and b are arbitrary, but I've added another example that is closer to yours in hopes that it helps. – Karl Kieninger Apr 14 '14 at 21:25
Your last example seems to me that it is the same in my OP.How does it differ because I can't see the difference – Jim Apr 15 '14 at 06:23
It not the same. You use JOIN..ON TableB.c_id IS NULL, wherease I use WHERE TableC.id IS NULL. I'd demo with sqlfiddle.com but it is down ATM. – Karl Kieninger Apr 15 '14 at 15:02
it back up http://sqlfiddle.com/#!2/d18f2c/25/0. A little confusing do to the limitation of the tools, but you should see the difference. – Karl Kieninger Apr 15 '14 at 16:03

Is left join commutative? What are its properties?

1 Answers1

Linked