Marketing Cloud - Retrieve data based on matching value

Question

I have a data extension that has email address along with some other data, but I need to get the persons first name and last name which exists in a different data extension.

What I need to do in the automation query is to take all email addresses from DE1 and match it up against the email address from DE2. From there, pull the FirstName and LastName values from DE2 and write the results to DE3 which will accept EmailAddress, FirstName, and LastName.

I'm looking at JOIN, INNER JOIN, UNION, OUTER JOIN... I have no idea which is the correct one to use or how that syntax should really look.

I assume it would be something like this overly simplified example:

SELECT EmailAddress, FirstName, LastName FROM DE2 JOIN EmailAddress FROM DE1

Answer 1

Really, it depends on what data you want returned.

Only the matching - INNER JOIN

All the records from left and matching - LEFT JOIN

All the records from right and matching - RIGHT JOIN

All the records - FULL OUTER JOIN

See below visualization for clarification and samples:

Answer 2

Based on the description, you can try with this:

SELECT A.EmailAddress, B.FirstName, B.LastName
FROM DE1 A
INNER JOIN DE2 B
ON A.EmailAddress = B.EmailAddress