How to select only rows that are not in another table without getting data of the other table - PLSQL

Question

Im trying to SELECT all rows from one table with a specific condition. Im using Oracle PLSQL.

I have two tables:

Books

• id
• name

Page

• id
• words
• book_id

One book can have multiple pages. I want to find all books that don't have any page with 50 words.

The only solution I've found is to make a Left Outer Join and then filter by nulls.

Select * from Books b 
LEFT OUTER JOIN Page p on b.id = p.book_id and p.words = 50
where p.words is null

I think its not a good solution, but it works, do you know any other way to do that?

I don't want to take any information about Pages. If I make a normal join and then I apply the condition I get N rows for a Book, and I only want to get 1 row for each Book

Thanks.

Answer 1

You could adjust your select so it is only b.*.

Possibly a more common alternative is not exists:

select b.*
from Books b 
where not exists (select 1
                  from Page p 
                  where b.id = p.book_id and p.words = 50
                 );

The two methods should have similar performance characteristics.