Select only if empty

Question

I have a user table and a table of things that they have on the list -

to show all users that have items in the list, I can join the two tables users and user_lists on user_id

eg.

 select u.emailaddr, u.name from users u join user_lists uw where u.user_id=uw.user_id group by u.name 

QUESTION: How to show all users who DO NOT have items in the list - to say it differently, I need a list of users who do not have an entry in the user_lists table

I tried this, but it ran endlessly

 select u.emailaddr, u.name from users u join user_lists uw where u.user_id<>uw.user_id group by u.name 

Answer 1

Use LEFT JOIN with IS NULL prefix:

 select u.emailaddr, u.name from users u LEFT join user_lists uw ON u.user_id = uw.user_id WHERE uw.user_id IS NULL; 

---

Or: NOT IN predicate:

 select u.emailaddr, u.name from users u WHERE u.user_id NOT IN (SELECT user_id FROM user_lists); 

Answer 2

 SELECT u.user_id FROM users u EXCEPT SELECT uw.user_id FROM user_lists uw 

it will give you identifiers that exist on users and do not exist on user lists.