By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE old_table (
name1 VARCHAR(50),
name2 VARCHAR(50),
origin_date DATE,
var1 VARCHAR(50),
today DATE
);
INSERT INTO old_table (name1, name2, origin_date, var1, today) VALUES
('red_1', 'red', '2010-01-01', 'aaa', '2020-01-01'),
('red_2', 'red', '2011-01-01', 'bbb', '2020-01-01'),
('blue_1', 'blue', '2005-01-01', 'ccc', '2020-01-01'),
('green_1', 'green', '2005-01-01', 'ddd', '2020-01-01');
CREATE TABLE new_table (
name1 VARCHAR(50),
name2 VARCHAR(50),
origin_date DATE,
var1 VARCHAR(50),
today DATE
);
INSERT INTO new_table (name1, name2, origin_date, var1, today) VALUES
('purple_1', 'purple', '2001-01-01', 'fff', '2020-01-02'),
('pink_1', 'pink', '2002-01-01', 'ggg', '2020-01-02'),
('red_1', 'red', '2010-01-01', 'aaa', '2020-01-02');
7 rows affected
select
coalesce(n.name1,o.name1) name1
, coalesce(n.name2,o.name2) name2
, coalesce(n.origin_date,o.origin_date) origin_date
, coalesce(n.var1,o.var1) var1
, case when n.name1 IS NULL then o.today end end_date
, case when n.name1 IS NULL then 'inactive' else 'active' end end_date
from new_table n
full outer join old_table o on n.name1 = o.name1
order by
coalesce(n.origin_date,o.origin_date) DESC
, coalesce(n.name1,o.name1)
| name1 | name2 | origin_date | var1 | end_date | end_date |
|---|---|---|---|---|---|
| red_2 | red | 2011-01-01 | bbb | 2020-01-01 | inactive |
| red_1 | red | 2010-01-01 | aaa | null | active |
| blue_1 | blue | 2005-01-01 | ccc | 2020-01-01 | inactive |
| green_1 | green | 2005-01-01 | ddd | 2020-01-01 | inactive |
| pink_1 | pink | 2002-01-01 | ggg | null | active |
| purple_1 | purple | 2001-01-01 | fff | null | active |