Select only if empty

Question

I have a table of users and a table of things they have on a list -

to show all users who have items on a list I can join the two tables users and user_lists on user_id

e.g.

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 do I show all users who DO NOT have items on a list - to say it another way, I need a list of users who do not have entries in table user_lists

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 predicate:

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: The NOT IN predicate:

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