table1 | id | value
--------------------
1 | john
2 | frank
3 | patel
4 | jim
table2 | id | value
--------------------
6 | steve
7 | tim
8 | sunny
9 | bhaskar
merged | tabid1 | tabid2
------------------------------
3 | 7
4 | 8
需要的输出:
output | tabid1 | tabval1 | tabid2 | tabval2
1 | john | NULL | NULL
2 | frank | NULL | NULL
3 | patel | 7 | tim
4 | jim | 8 | sunny
NULL | NULL | 6 | steve
NULL | NULL | 9 | bhaskar
我试过了:
SELECT *
FROM table1
LEFT JOIN merged m1 ON table1.id = m1.tabid1, merged m2
RIGHT JOIN table2 ON table2.id = m2.tabid2
但是它给出了16行而不是需要的6行
最佳答案
select table1.id,table1.value,table2.id,table2.value
from table1 left join merged on table1.id=merged.tabid1
left join table2 on merged.tabid2=table2.id
union
select table1.id,table1.value,table2.id,table2.value
from table2 left join merged on table2.id=merged.tabid2
left join table1 on merged.tabid1=table1.id ;
或具有正确的加入:
SELECT * FROM
table1 LEFT JOIN merged on table1.id=merged.tabid1
LEFT JOIN table2 ON merged.tabid2=table2.id
UNION
SELECT * FROM
table1 RIGHT JOIN merged on table1.id=merged.tabid1
RIGHT JOIN table2 ON merged.tabid2=table2.id WHERE table1.id IS NULL;
关于mysql - 如何使用中间表在2个表上应用外部联接?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9090804/