CODEWITHSUNDEEP
hivehiveql
sharath
sharath

Reputation: 3616

Hive combining full outer join and not in?

I have a hive query which looks like this

SELECT 
    a.uid, 
    a.order_id
FROM table_a a
FULL OUTER JOIN 
(
    SELECT 
        uid, 
        order_id
    FROM table_b
) b
ON (a.uid = b.uid AND a.order_id = b.order_id)

This query results in a set of uids and order_ids.

Now, I have a black_listed table which has a set of uids. I want to have this set of black listed uids not to be part of the final result.

Is there a way I can add this remove-blacklisted-uids subquery to the above query (do this in a single query)

So if I have a table called black_list with uid1 and uid2, both these uids should not be part of my final result of the first query.

Upvotes: 0

Views: 306

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

This can be done with a left join.

SELECT 
    a.uid, 
    a.order_id
FROM table_a a
FULL OUTER JOIN 
(
    SELECT 
        uid, 
        order_id
    FROM table_b
) b
ON (a.uid = b.uid AND a.order_id = b.order_id)
LEFT JOIN black_listed bl on bl.id = a.uid 
WHERE bl.id IS NULL

Upvotes: 1

Related Questions