with T1 AS (select id,max(dt) as newdt from a group by id), T2 AS (select id,name from a where dt in (select newdt from T1)), T3 AS (select id,null code,name from T2), T4 AS (select id,code,null name from b), T5 AS (select * from T3 union all select * from T4) select id,max(name) name,max(code) code from T5 group by id order by id