Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1081
  • Last Modified:

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
0
mattphung
Asked:
mattphung
1 Solution
 
Aneesh RetnakaranDatabase 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now