mysql如何将两个表外链接,如何做完全外连接来组合mysql中的两个表？

I have two tables payroll_advance and payroll_advrtn,and i supposed to do full outer join to get my require result.But,I'm sure full outer join isn't possible in mysql and also i know that full outer join is possible by using the union.But i don't know how can i do join at the following query.

My payroll_advance table produce the following result.

SELECT _id,_tid,_dt,sum(_amount) as _advance FROM payroll_advance WHERE YEAR( _dt )=YEAR(CURDATE()) AND MONTH(_dt) = MONTH(CURDATE()) group by _tid;

+-----+-------+------------+---------+

| _id | _tid | _dt | _advance|

+-----+-------+------------+---------+

| 17 | hjg | 2012-04-18 | 2151 |

| 22 | RKT01 | 2012-04-10 | 2098 |

| 14 | RKT04 | 2012-04-18 | 1511 |

| 16 | RKT09 | 2012-04-09 | 250 |

| 15 | RKT10 | 2012-04-17 | 1313 |

| 8 | RKT21 | 2012-04-03 | 1321 |

| 19 | RKT31 | 2012-04-26 | 2512 |

| 20 | RKT33 | 2012-04-10 | 2250 |

| 25 | T01 | 2012-04-11 | 2500 |

+-----+-------+------------+---------+

And payroll_advrtn gives the following result.

SELECT _id,_tid,_dt,sum(_amount) as _advrtn FROM payroll_advrtn WHERE YEAR( _dt ) = YEAR(CURDATE()) AND MONTH(_dt) = MONTH(CURDATE()) group by _tid;

+-----+-------+------------+---------+

| _id | _tid | _dt | _advrtn |

+-----+-------+------------+---------+

| 9 | RKT02 | 2012-04-10 | 2500 |

| 8 | RKT04 | 2012-04-20 | 150 |

+-----+-------+------------+---------+

But i want something like the following result by combining the above two result.

+------+-------+-------+------------+----------+---------+

| _id | _tid | _tid | _dt | _advance | _advrtn |

+------+-------+-------+------------+----------+---------+

| 17 | hjg | NULL | 2012-04-18 | 2151 | NULL |

| 22 | RKT01 | NULL | 2012-04-10 | 999 | NULL |

| 14 | RKT04 | RKT04 | 2012-04-18 | 11 | 150 |

| 16 | RKT09 | NULL | 2012-04-09 | 250 | NULL |

| 15 | RKT10 | NULL | 2012-04-17 | 1313 | NULL |

| 8 | RKT21 | NULL | 2012-04-03 | 1321 | NULL |

| 19 | RKT31 | NULL | 2012-04-26 | 2512 | NULL |

| 20 | RKT33 | NULL | 2012-04-10 | 2250 | NULL |

| 25 | T01 | NULL | 2012-04-11 | 2500 | NULL |

| NULL | NULL | RKT02 | NULL | NULL | 2500 |

+------+-------+-------+------------+----------+---------+

Any help will be appreciated.Thanks!!

解决方案

In order to do the FULL OUTER JOIN you can do the LEFT OUTER JOIN and UNION with RIGHT OUTER JOIN (provided that MySql still does not support FULL OUTER JOIN):

select * from A as a

left outer join B as b on a.col = b.col

union

select * from A as a

right outer join B as b on a.col = b.col

Note that you can use subqueries for A and B - which should work with your queries. In your case:

select * from (SELECT * FROM t1) as a

left outer join (SELECT * FROM t2) as b on a._tid = b._tid

union

select * from (SELECT * FROM t1) as a

right outer join (SELECT * FROM t2) as b on a._tid = b._tid

With result being equal to (provided that I didn't my a mistake in copy-pasting your data):

+------+-------+------------+----------+------+-------+------------+----------+

| _id | _tid | _dt | _advance | _id | _tid | _dt | _advartn |

+------+-------+------------+----------+------+-------+------------+----------+

| 17 | hjg | 2012-04-18 | 2151 | NULL | NULL | NULL | NULL |

| 22 | RKT01 | 2012-04-10 | 2098 | NULL | NULL | NULL | NULL |

| 14 | RKT04 | 2012-04-18 | 1511 | 8 | RKT04 | 2012-04-20 | 150 |

| 16 | RKT09 | 2012-04-09 | 250 | NULL | NULL | NULL | NULL |

| 15 | RKT10 | 2012-04-17 | 1313 | NULL | NULL | NULL | NULL |

| 8 | RKT21 | 2012-04-03 | 1321 | NULL | NULL | NULL | NULL |

| 19 | RKT31 | 2012-04-26 | 2512 | NULL | NULL | NULL | NULL |

| 20 | RKT33 | 2012-04-10 | 2250 | NULL | NULL | NULL | NULL |

| 25 | T01 | 2012-04-11 | 2500 | NULL | NULL | NULL | NULL |

| NULL | NULL | NULL | NULL | 9 | RKT02 | 2012-04-10 | 2500 |

+------+-------+------------+----------+------+-------+------------+----------+