Consolidate union result

I am selecting email address from two source tables via a union query. Normally, I would leverage union’s behavior of filtering out duplicate values, but in this case, each source table may or may not have a value for each person, and I have to give “priority” to the email address from the first source table when it exists.

When I encounter a situation where the same email address exists in both sources, I would like to to omit the line from source 2 where the email matches that from source 1, as shown below. Assuming CURRENT DATA, how can I make a new selection from CURRENT DATA to arrive at DESIRED RESULT?

CURRENT DATA

PERSONID	EMAILADDRESS	SOURCE
7538583	email@example	1
7538583	email@example	2
7538583	person@somecompany	2

DESIRED RESULT

PERSONID	EMAILADDRESS	SOURCE
7538583	email@example	1
7538583	person@somecompany	2

Answer

You can use a subquery in the second part of the union:

select personid,
       emailaddress,
       1 as source
  from table1

union

select personid,
       emailaddress,
       2 as source
  from table2
 where not exists (select 1 from table1 where table1.personid = table2.personid);