<p>Suppose we have a table A:</p>

<pre><code>itemid mark
1      5
2      3
</code></pre>

<p>and table B:</p>

<pre><code>itemid mark
1      3
3      5
</code></pre>

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

<pre><code>itemid A.mark B.mark
1      5      3
2      3      NULL
3      NULL   5
</code></pre>

<p>Is there a way to do it in one query in MySQL?</p>

[{'answer_id': 9625, 'author': 'roo', 'author_id': 716, 'author_profile': 'https://Stackoverflow.com/users/716', 'pm_score': 2, 'selected': False, 'text': '<p>Could do with some work but here is some sql</p>\n\n<pre><code>select distinct T.itemid, A.mark as "A.mark", B.mark as "B.mark"\n    from (select * from A union select * from B) T \n    left join A on T.itemid = A.itemid \n    left join B on T.itemid = B.itemid;\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 9626, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': -1, 'selected': False, 'text': '<p>This works for me on SQL Server:</p>\n\n<pre><code>select isnull(a.id, b.id), a.mark, b.mark\nfrom a \nfull outer join b on b.id = a.id\n</code></pre>\n'}, {'answer_id': 9635, 'author': 'Nickolay', 'author_id': 1026, 'author_profile': 'https://Stackoverflow.com/users/1026', 'pm_score': 4, 'selected': True, 'text': '<p>It\'s called a full outer join and it\'s not supported natively in MySQL, judging from its <a href="http://dev.mysql.com/doc/refman/4.1/en/join.html" rel="noreferrer">docs</a>. You can work around this limitation using UNION as described in the comments to the page I linked to.</p>\n\n<p>[edit] Since others posted snippets, here you go. You can see explanation on the linked page.</p>\n\n<pre><code>SELECT *\nFROM A LEFT JOIN B ON A.id = B.id\nUNION ALL\nSELECT *\nFROM A RIGHT JOIN B ON A.id = B.id\nWHERE A.id IS NULL\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/9614', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/844/']