SQL union seems to fail for me

I haven’t done any SQL queries in years.
I ran into an issue that looks to me pretty straightforward, but I cannot seem to solve it in reasonable time.
Let’s say that I have two tables that I pull from the database using the select statement:

    table1:
    id name    status
    1  record1 ACTIVE
    2  record2 ACTIVE

    table2:
    id name    status
    1  record3 DELETED
    2  record1 DELETED

I would like to have the third table that looks like this:

    table3:
    id name    status
    1  record1 ACTIVE
    2  record2 ACTIVE
    3  record3 DELETED

So basically, if the column is present in the "ACTIVE" table, it must be shown in the third one (table3) as ACTIVE regardless of the column that has the same name in "DELETED" table. Also, if the column with specific name is present in the "DELETED" table, but not in the "ACTIVE", it should be shown too as DELETED.

I hope I’ve explained the issue clearly enough.
It’s my first time posting on stackoverflow, and I hope I’ll get some help from you guys.
Thanks! 🙂

select a.*
from actives a
union all
select d.*
from deletes d
where not exists (select 1 from actives a where a.name = d.name);