sql - Hive: substract one table from another? -

in hive have 2 tables:

'old_books'   title string, author string, year int, outofprint boolean;

and

'new_books'   title string, author string, year int;

by mistake created these tables have put new titles 'old_books' table.

is possible substract records exist in both tables 'old-books' table means of hive only?

so far have manged select books exist in both tables hive request:

select old_books.* old_books join new_books on (old_books.title=new_books.title);

how substruct result of request 'old_books' ?

assuming have hive 01.3 version or later, can use not exists clause:

select * old_books not exists ( select 1 old_books b join new_books c on (b.title=c.title) a.book_id = b.book_id);

Hide