Trying to combine two DE and getting violation of primary key error

Question

I'm trying to combine two data extensions into a master data extension. We use email address as a primary key. I setup the query below:

select
[EmailAddress] AS [Email],[FirstName] AS [First Name],[LastName] AS [Last Name],[Account] AS [Company Name],[Job Title] AS [Job Title],[Phone] AS [Phone],[Address] AS [Address],[City] AS [City],[State] AS [State Province],[ZipCode] AS [Zip Code],[Country] AS [Country]
from Filter_CorporateCustomer
union
select
[Email] AS [Email],[First Name] AS [First Name],[Last Name] AS [Last Name],[Account] AS [Company Name],[Job Title] AS [Job Title],[Phone] AS [Phone],[Street Address] AS [Address],[City] AS [City],[State Province] AS [State Province],[Zip Postal Code] AS [Zip Code],[Country] AS [Country]
from E_Corporate

The query fails: Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object.

I obviously have duplicate emails across the two data extension. Is there a best practice for SQL that I can add the data from the first but not the second if its a duplicate?

Answer 1

Give this a go the left joining should be email and not emailaddress

select
[EmailAddress] AS [Email],[FirstName] AS [First Name],[LastName] AS [Last Name],[Account] AS [Company Name],[Job Title] AS [Job Title],[Phone] AS [Phone],[Address] AS [Address],[City] AS [City],[State] AS [State Province],[ZipCode] AS [Zip Code],[Country] AS [Country]
from Filter_CorporateCustomer f
LEFT JOIN E_Corporate e ON  f.[EmailAddress] = e.[Email] 
WHERE e.[Email] IS NULL

Answer 2

To avoid a primary violation Try this:

select
[EmailAddress] AS [Email],[FirstName] AS [First Name],[LastName] AS [Last Name],[Account] AS [Company Name],[Job Title] AS [Job Title],[Phone] AS [Phone],[Address] AS [Address],[City] AS [City],[State] AS [State Province],[ZipCode] AS [Zip Code],[Country] AS [Country]
from Filter_CorporateCustomer f
LEFT JOIN
E_Corporate e 

ON  f.[EmailAddress] = e.[EmailAddress] 

WHERE e.[EmailAddress] IS NULL

This will exclude contacts that are already present in the first DE.