NOT IN not working SQL

Question

I have 2 tables , Users and Friends Table, Users have uID, Friends have UID as well, I want to check the UIDs present in Users table but not present in Friends Table, I am using this query

SELECT b.userId 
FROM users b 
where b.userId NOT IN(
  select userId 
  from user_friend
)

But it is returning me all the rows of Users Table.. Am I missing something? Example Data in MYSQL

Users Table
1
2
3
4
Friends Table
2
3
4

I actually need to get 1

Give example of the data in your two tables. Also, are you using mysql or oracle? — Taemyr, Oct 21 '13 at 09:21
@IanKenney The schema is pretty straight forward..Both Have UID as PK — , Oct 21 '13 at 09:27
Are there `NULL` values in the `userid` column in the friends table? — ypercubeᵀᴹ, Oct 21 '13 at 09:30
is there any difference between user_friend and Friends table, since in query you have written that and schema you have written Friends — Gopesh Sharma, Oct 21 '13 at 09:35
@GopeshSharma no there is no difference, that was just to make ppl understand — , Oct 21 '13 at 09:37
Is it `UID` or `userid`? Is it `Friends` or `user_friend`? Make up your mind on the names of tables and columns and post a few rows (with all the columns of the tables) and someone will help you. — ypercubeᵀᴹ, Oct 21 '13 at 09:38
Can you please tell us what this returns? `SELECT COUNT(*) FROM user_friend WHERE userid IS NULL;` ? — ypercubeᵀᴹ, Oct 21 '13 at 09:45
@Taemyr Yes it would matter. If there is (even one NULL) in the friends table, no row will be returned by the query (and the way to solve the problem is to rewrite the query with NOT EXISTS, as Tippa's answer.) The strange thing is that the OP says that the query returns **all rows** and not 0 rows. It's confusing - and the only possible explanation is that the OP has not provided accurate information. — ypercubeᵀᴹ, Oct 21 '13 at 10:00
@Taemyr See: **[SQL NOT IN not working](http://stackoverflow.com/questions/5231712/sql-not-in-not-working)** — ypercubeᵀᴹ, Oct 21 '13 at 10:05

score 1 · Accepted Answer · answered Oct 21 '13 at 09:35

1

Try this

SELECT b.userId 
FROM users b 
WHERE NOT EXISTS (
   SELECT * 
   FROM user_friend
   WHERE b.userId = user_friend.userId
);

answered Oct 21 '13 at 09:35

Tippa Raj

584
4
8

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS ( SELECT * FROM user_friend ) LIMIT 0, 30' at line 3 – Oct 21 '13 at 09:36
@TippaRaj It would have been better, if an explanation was given on why the `NOT IN` did not work. – Ravinder Reddy Oct 21 '13 at 09:47

score 0 · Answer 2 · edited Oct 21 '13 at 09:31

0

Try this one:

SELECT b.userId 
FROM users b 
where b.userId NOT IN(
  select user_friend.userId 
  from user_friend
)

edited Oct 21 '13 at 09:31

Taemyr

3,407
16
26

answered Oct 21 '13 at 09:28

Max

33
6

same result.. there is nothing different – Oct 21 '13 at 09:30

score 0 · Answer 3 · answered Oct 21 '13 at 09:41

0

A Work Around for your query

SELECT  U.UserId
FROM    Users U
EXCEPT
SELECT  F.UserId
FROM    Friends F

answered Oct 21 '13 at 09:41

Gopesh Sharma

6,730
4
25
35

I think U2 should be user_friend? Otherwise this should always return no rows. – Taemyr Oct 21 '13 at 09:42

NOT IN not working SQL

3 Answers3