This question already has answers here: Closed 12 years ago.

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开发者_如何学Go - null

What type of join do I need to use?

Cheers,

Ben

---

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