MySQL - merging 2 tables keeping all rows

I would like to merge 2 tables keeping all rows in both tables like doing a left and a right join at the same t开发者_StackOverflow中文版ime. See example below. Column 'fruit' is common to both tables and I want to list the number of fruit in both tables. Also a particular fruit may appear in one table but not the other. Can anyone help? Thanks.

TABLE1                    TABLE2                
fruit, number             fruit, number         
-------------             -------------         
apples,  1                apples,   10          
pears,   2                oranges,  30          


MERGED TABLE (this is the result I'm after:
fruit, number_table1, number_table2
--------------------------------------
apples,     1,      10
pears,      2,      -
oranges,    -,      30

And here is the code to create the tables if you need to try it out....

CREATE TABLE table1 (fruit CHAR(10) NOT NULL, number INT(10) NOT NULL);
CREATE TABLE table2 (fruit CHAR(10) NOT NULL, number INT(10) NOT NULL);
insert into table1 (fruit, number) values ('apples', 1), ('pears', 2);
insert into table2 (fruit, number) values ('apples', 10), ('oranges', 30);

---

Since MySQL doesn't have a FULL OUTER JOIN, you can emulate it since MySQL 4 using UNION:

SELECT t1.fruit, t1.number, t2.number
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t2.fruit = t1.fruit
UNION
SELECT t2.fruit, t1.number, t2.number
FROM Table1 AS t1
RIGHT JOIN Table2 AS t2 ON t2.fruit = t1.fruit

---

Here is a solution using UNION:

(select table1.fruit fruit, table1.number number1, table2.number number2 from table1 left join table2 using (fruit)) union (select table2.fruit fruit, table1.number number1, table2.number number2 from table2 left join table1 using (fruit));

---

insert into mergetable
    (fruit, number_table1, number_table2)
    select t1.fruit, t1.number, t2.number
        from table1 t1
            left join table2 t2
                on t1.fruit = t2.fruit
    union
    select t2.fruit, t1.number, t2.number
        from table2 t2
            left join table1 t1
                on t2.fruit = t1.fruit