CODEWITHSUNDEEP
hive
Badoe
Badoe

Reputation: 1640

How to write "where" clause in hive to filter out values in another table

I have a table "mytable" contains several columns including one called "ip." There is another table called "bots" with only one column "bot", which is a list of several ip address values. I want to filter out all rows in mytable with "ip" value in table bots.

I wrote 

select * from mytable
where ip not in (select bot from bots);

Apparently this is not the way to do it. I am wondering what is the correct syntax.

I googled around but the keywords here "where" and "in" are too common in the English language and hence hard to find any useful results.

Upvotes: 0

Views: 2711

Answers (1)

Joe K
Joe K

Reputation: 18424

select mytable.* from
mytable left outer join bots on mytable.id = bots.bot
where bots.bot is null

Upvotes: 1

Related Questions