CODEWITHSUNDEEP
mysql
Red Virus
Red Virus

Reputation: 1707

Query issue when using NOT IN and the sub-select returns NULLs

I' am trying to figure it out what am doing wrong in here. Basically the Query below Queries the table "Profiles" t1 and then again Using NOT IN Function Queries same table "Profiles" and gets the profile_friends from t2 and shows the results of ids that are not found on t2 results.

Query

SELECT t1.profile_id,  t1.profile_username, t1.profile_name, t1.profile_friends 
FROM PROFILES t1
WHERE t1.profile_id NOT IN (SELECT t2.profile_friends FROM PROFILES AS t2 WHERE t2.profile_id = '1')

Results from above Query and Database Structure enter image description here

This ID's 2,6,10 should not show in this result. Well ID 2 is not showing but why is ID 6 and 10 are showing?

But When I try this code it works (Note that I have hard coded the values)

SELECT t1.profile_id,  t1.profile_username, t1.profile_name, t1.profile_friends 
FROM PROFILES t1
WHERE t1.profile_id NOT IN (2,6,10)

Upvotes: 0

Views: 44

Answers (1)

peterm
peterm

Reputation: 92785

You have to take NULLs into consideration. Any usual comparison (=, <>, >, <) with NULL gives NULL. The proper comparison for NULL is IS NULL

Also it looks like there is no need for a subquery in your case.

SELECT *
  FROM profiles
 WHERE profile_id <> 1
   AND (profile_friends <> '1' OR profile_friends IS NULL) -- parentheses are important here

Sample output:

| PROFILE_ID | PROFILE_USERNAME | PROFILE_FRIENDS |
|------------|------------------|-----------------|
|          3 |        username3 |          (null) |
|          4 |        username4 |          (null) |
|          5 |        username5 |          (null) |
|          7 |        username7 |          (null) |
|          8 |        username8 |          (null) |
|          9 |        username9 |          (null) |

Here is a SQLFiddle demo

Upvotes: 2

Related Questions