select * FROM(SELECT a.id,a.name,t2.code from (SELECT id,max(name) as name,MAX(dt) as dt from t1 GROUP BY id) a left join t2 on a.id=t2.id ) x UNION SELECT * FROM (SELECT t2.id,a.name,t2.code from t2 left join (SELECT id,max(name) as name,MAX(dt) as dt from t1 GROUP BY id) a on t2.id=a.id) y;