HIVE check for data from table 1 which is absent in table 2

Question

I am looking to create a HIVE SQL query to find all the values from table 1 which ARE NOT present in table 2. I understand that I need to use a join however I cannot figure out how to implement it for this situation...

Thanks, James

for example:

Table1

url                  number
xe.com               5
google.com           2
ebay.co.uk           6

Table2

url                  visits
facebook.com         8
google.com           4
ebay.co.uk           15

So for example the query should return all values from Table1 which are present in Table2, i.e.

url                  number         visits
google.com           2              4
ebay.co.uk           6              15

Answer 1

A LEFT JOIN will return all rows from Table1 regardless of whether or not there's a match. In the case that there isn't a match the columns from Table2 will have the value NULL - these are the rows you want:

SELECT Table1.url, Table1.number
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.url = Table2.url
WHERE Table2.url IS NULL