CODEWITHSUNDEEP
mysqlsqljoinleft-join
StudioTime
StudioTime

Reputation: 23989

Select only if empty

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

Upvotes: 7

Views: 609

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

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);

Upvotes: 11

user2202158
user2202158

Reputation: 1

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

it will give you the ids that exist in users and don't exist in userlists.

Upvotes: 0

Related Questions