SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table a (id int, name varchar(10), description varchar(20));insert into a values (1,'A', 'descr A');insert into a values (2,'B', 'descr B');insert into a values (3,'C', 'descr C');insert into a values (4,'D', 'descr D');insert into a values (5,'E', 'descr E');insert into a values (6,'F', 'descr F');insert into a values (7,'G', 'descr G');insert into a values (8,'H', 'descr H');insert into a values (9,'I', 'descr I');insert into a values (10,'J', 'descr J');
10 rows affected

create table b ( id int, name varchar(10), description varchar(20));insert into b values (1,'A', 'descr A');insert into b values (2,'B', 'descr B');insert into b values (3,'M', 'descr M');insert into b values (4,'N', 'descr N');insert into b values (5,'O', 'descr O');insert into b values (6,'C', 'descr C');insert into b values (7,'D', 'descr D');insert into b values (8,'E', 'descr E');insert into b values (9,'G', 'descr G');insert into b values (10,'H', 'descr H');insert into b values (11,'I', 'descr i');insert into b values (12,'J', 'descr j');
12 rows affected

select 
   coalesce(a.name, b.name) name,
   coalesce(a.description, b.description) description,
  case
    when a.name is null then 'newly added'
    when b.name is null then 'removed'
  end status
from  a
full join b on b.name = a.name 
order by isnull(b.id,(select tr.id from b tr where description = (select TOP 1 description from a t where t.id < a.id order by t.id desc) ))


name
description
status


A
descr A
null

B
descr B
null

M
descr M
newly added

N
descr N
newly added

O
descr O
newly added

C
descr C
null

D
descr D
null

E
descr E
null

F
descr F
removed

G
descr G
null

H
descr H
null

I
descr I
null

J
descr J
null

name	description	status
A	descr A	null
B	descr B	null
M	descr M	newly added
N	descr N	newly added
O	descr O	newly added
C	descr C	null
D	descr D	null
E	descr E	null
F	descr F	removed
G	descr G	null
H	descr H	null
I	descr I	null
J	descr J	null