Hive select from two tables where fields match?

Question

I have two tables, both have email and name fields. How to write Hive query that will return all recocrds that have matching e-mails in both tables&

For example:

 Table_1 :
   name x, email e1 
   name y, email e2

Table_2:
  name z, email e3 
  name q, email e2

Result:
  name q, email e2
  name y, email e2

Answer 1

Use a JOIN. Check this manual page on info about JOINS.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

You can write a query like:

SELECT * FROM Table_1
LEFT OUTER JOIN Table_2 ON Table_1.email = Table_2.email
LEFT OUTER JOIN Table_3 ON Table_2.email = Table_3.email;

This query will return you all the records with matching emails.