mysql 全连接查询合并字段

1、创建两张表

t1如下：

id	name
1	a1
3	a3

t2如下：

id	name
1	b1
2	b2
5	b5

联合查询合并字段：

sql语句：

SELECT t3.`name` AS B, t3.`name1` AS C ,COALESCE(t3.id2,t3.id1)  AS myId

FROM (SELECT t1.id as id2 ,t1.`name` as name,t2.id as id1 ,t2.`name` as name1 FROM t1 LEFT JOIN t2 on t1.id=t2.id 

UNION SELECT t1.id as id2 ,t1.`name` as name,t2.id as id1 ,t2.`name` as name1  from  t1 RIGHT JOIN t2 on t1.id=t2.id) as t3 GROUP BY  myId;

查询结果：

B	C	myID
a1	b1	1
	b2	2
a3		3
	b5	5