SQL Query Performance Enhancement on NOT IN

Question

Suppose I have a query need to lookup users who bought book A but not bought book B.

Typical SQL query can be:

select u.user_name
from User u 
join Purchase p on u.id=p.user_id
join Book b on p.purchase_item=b.id
where b.name='book_A'
and user_name not in
(
select u.user_name
from User u 
join Purchase p on u.id=p.user_id
join Book b on p.purchase_item=b.id
where b.name='book_B'
)

This not in query looks not efficient, any enhancement I can do this query?

Answer 1

Try using exists clause instead of IN clause -

SELECT u.user_name
  FROM Purchase p
  JOIN Book b ON p.purchase_item=b.id
  JOIN User u ON u.id=p.user_id
 WHERE b.name='book_A'
   AND NOT EXISTS (SELECT NULL
                     FROM Purchase p2
                     JOIN Book b ON p.purchase_item=b.id
                    WHERE b.name='book_B'
                      AND p.user_id = p2.user_id)

Apart from this you my try having index on below columns -

User - id
Purchase - user_id, purchase_item
Book - id, name