mattphung
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'd expect Code 1 to return no results
and
Code 2 to return all rows where the UserID is not in user_location
and
Code 2 to return all rows where the UserID is not in user_location
For this
SELECT * FROM [User]
WHERE (Which field you are checking is missing here ) NOT EXISTS (SELECT userid FROM [user_location])
GIVES NO RESULTS
SELECT * FROM [User]
WHERE (Which field you are checking is missing here ) NOT EXISTS (SELECT userid FROM [user_location])
GIVES NO RESULTS
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
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.
ASKER
shasunder:
Your code gave a syntax error. Thanks anywhere.
aneeshattingal: solution was the best one
Your code gave a syntax error. Thanks anywhere.
aneeshattingal: solution was the best one
SELECT * FROM [User]
WHERE UserId NOT IN (SELECT userid FROM [user_location])