0

I have a table of users with an id column and a table of posts that have a user_id column.

I'm looking to find all users that have never posted.

Pseudo Code:

SELECT u.* FROM users u
JOIN posts p 
WHERE u.id IS NEVER IN p.user_id

I think that I'm missing something simple here and can't find the answer. Thanks for any help you can provide.

Pramod Yadav
  • 245
  • 2
  • 20
Paul
  • 328
  • 1
  • 5
  • 17

5 Answers5

4

Use LEFT JOIN:

SELECT u.* 
FROM users u
LEFT JOIN posts p ON u.id = p.user_id 
WHERE p.user_id is null;

LEFT JOIN will give you those users even if they have not posted any posts, those users will have p.user_id equal to null. So adding WHERE p.user_id is null will give you those users who never posted any posts.

  • Thanks, looks like you posted just before I did. Any difference between the LEFT JOIN and sub query method that you might see. Both are producing similar speed results for me. – Paul Sep 23 '17 at 17:27
  • @MinistryofChaps suggested this link to explain what is better. https://stackoverflow.com/questions/2577174/join-vs-sub-query – Paul Sep 23 '17 at 17:37
1

You can try this-

SELECT u.* FROM users u
where u.id not in (select p.user_id from posts p);
Lokesh Kumar Gaurav
  • 726
  • 1
  • 8
  • 24
1

This is your solution:

SELECT u.* FROM users u WHERE u.id NOT IN (SELECT p.user_id FROM posts p)

Somewhat explanatory, checks the user IDs in users that are not in posts.

MinistryOfChaps
  • 1,458
  • 18
  • 31
1

you could use a NOT IN and a subselect 

  SELECT u.* 
  FROM users u
  where u.id  NOT IN (
    select user_id 
    from posts
  )
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
1

Try using Not in

SELECT u.* FROM users u
JOIN posts p 
WHERE u.id IS NEVER IN p.user_id
NOT IN (SELECT p.user_id FROM posts )
nitin.sharma0180
  • 471
  • 3
  • 6