Bidirectional outer join

Question

Suppose we have a table A:

itemid mark
1      5
2      3

and table B:

itemid mark
1      3
3      5

I want to join A*B on A.itemid=B.itemid both right and left ways. i.e. result:

itemid A.mark B.mark
1      5      3
2      3      NULL
3      NULL   5

Is there a way to do it in one query in MySQL?

Answer 1

Could do with some work but here is some sql

select distinct T.itemid, A.mark as "A.mark", B.mark as "B.mark"
    from (select * from A union select * from B) T 
    left join A on T.itemid = A.itemid 
    left join B on T.itemid = B.itemid;

This relies on the left join, which returns all the rows in the original table (in this case this is the subselect table T). If there are no matches in the joined table, then it will set the column to NULL.

Answer 2

This works for me on SQL Server:

select isnull(a.id, b.id), a.mark, b.mark
from a 
full outer join b on b.id = a.id

EDIT:

@Nickolay interesting that it's not supported on MySQL. I guess it's something of an edge case.

Answer 3

It's called a full outer join and it's not supported natively in MySQL, judging from its docs. You can work around this limitation using UNION as described in the comments to the page I linked to.

[edit] Since others posted snippets, here you go. You can see explanation on the linked page.

SELECT *
FROM A LEFT JOIN B ON A.id = B.id
UNION ALL
SELECT *
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL