SQL Exists function help

Why does code 1 below give different results than code 2? Which one should I trust?

CODE 1
SELECT   * FROM   [User]
WHERE   NOT EXISTS (SELECT     userid FROM [user_location])
GIVES NO RESULTS

CODE 2
SELECT   * FROM   [User]
WHERE   UserId NOT IN (SELECT  userid FROM [user_location])
GIVES SOME RESULTS
mattphungAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Hello mattphung,

Code 2  or this one

SELECT   * FROM   [User] u
WHERE   NOT EXISTS (SELECT     userid FROM [user_location] ul where u.userID = ul.UserID )



Aneesh R
0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
only this syntax is correct

SELECT   * FROM   [User]
WHERE   UserId NOT IN (SELECT  userid FROM [user_location])
0
 
messen1975Commented:
I'd expect Code 1 to return no results
and
Code 2 to return all rows where the UserID is not in user_location
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Shanmuga SundaramDirector of Software EngineeringCommented:
For this
SELECT   * FROM   [User]
WHERE  (Which field you are checking is missing here )  NOT EXISTS (SELECT     userid FROM [user_location])
GIVES NO RESULTS
0
 
messen1975Commented:
I'd right that (Assuming you want all Users Not in user_location) as


Select * 
From User left Join User_Location on user.UserID = user_location.userid
Where user_location.userid is null

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Code 1 doesn't return results because it's incomplete. Need to join user_location to user, like aneeshattingal wrote. So you should trust Code 2.
0
 
mattphungAuthor Commented:
shasunder:
 Your code gave a syntax error.  Thanks anywhere.

aneeshattingal: solution was the best one
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.