I’m using Mysql.
I have table A(ida) and table B(idb) and a cross reference table CR(ida, idb).
I want to capture in a query the relationship between A and B (basically what’s is CR) but I also want all the rows from A and B that have no relations present in CR. To get something like this:
ida | idb ========= X | nul Y | 1 nul | 2
I tried this:
select * from A left join CR on (...) right join B on (...)
This way I get all rows from B, but not all from A.
If I do A left join CR left join B I get all from A but not all from B. Which is expected. But I don’t get why the right join obscures the left join in the previous case. I also used braces to try to force some precedence in the joins but the result was the same.
Advertisement
Answer
Joins are evaluated left to right. Your query’s order of operations is as if if you used parentheses like this:
(A LEFT JOIN CR) RIGHT JOIN B
Therefore it’s bound to return all rows in B.
But it will only return matching rows from (A LEFT JOIN CR). That part of the join will include all rows from A, but depending on the join condition of the subsequent right join, some of those A rows may be excluded.
From your description, it sounds like you really want a FULL OUTER JOIN. MySQL does not support this type of outer join. There are ways to simulate it by using a UNION of two joins:
... A LEFT OUTER JOIN B UNION A RIGHT OUTER JOIN B ...
The feature request of MySQL to support FULL OUTER JOIN was filed in 2006. If you need this, you should go log into the bug tracker and click “Affects Me” on that feature request.