表:a: id name age other
b: id name age other
SELECT * FROM b LEFT JOIN a ON a.age=b.age
UNION
SELECT * FROM b RIGHT JOIN a ON a.age=b.age
如何要把查询结果写入另一个表中,则需要把相同列合并,因为查询结果会生成两个name列,可能一个会为空,所以:
SELECT (CASE WHEN b.name is null THEN a.name ELSE b.name END)name,(CASE WHEN b.age is null THEN a.age ELSE b.age END)age FROM b LEFT JOIN a ON a.age=b.age
UNION
SELECT (CASE WHEN b.name is null THEN a.name ELSE b.name END)name,(CASE WHEN b.age is null THEN a.age ELSE b.age END)age FROM b RIGHT JOIN a ON a.age=b.age