SQL Join to return null rows from both left and right table

Question

Possible Duplicate:
MySQL: FULL OUTER JOIN - How do I merge one column?

Real simple question, but have completely confused myself.

Say I have the following tables:

tbl1

id - count1
a - 7
b - 4
d - 2

tbl2

id - count2
a - 3
c - 4

I want to create a table that gives the following result:

tbl_temp

id - count1 - count2
a - 7 - 3
b - 4 - null
c - null - 4
d - 2 - null

What type of join do I need to use?

Cheers,

Ben

Answer 1

Ideally you would want to use a FULL OUTER JOIN but it is not supported in MySQL. Instead you can emulate it using a LEFT and RIGHT OUTER JOIN (or another LEFT with the tables reversed) and UNION ALL the results.

SELECT
    tbl1.id,
    tbl1.count1,
    tbl2.count2
FROM tbl1
LEFT JOIN tbl2
ON tbl1.id = tbl2.id

UNION ALL

SELECT
    tbl2.id,
    tbl1.count1,
    tbl2.count2
FROM tbl2
LEFT JOIN tbl1
ON tbl1.id = tbl2.id
WHERE tbl1.id IS NULL

ORDER BY id

Results:

id  count1  count2
a   7       3     
b   4       NULL  
c   NULL    4     
d   2       NULL