CODEWITHSUNDEEP
sqljoin
mrblah
mrblah

Reputation: 103607

sql to return unique list of UserID's based on 2 tables

(not discussing the table design, its a done deal!)

I know how to do this query using a WHERE NOT IN clause, but I was wondering how it could be done using a join.

Note: rows are not unique, so we have to do a DISTINCT so as to not get duplicates.

Users (userID)
OldUsers(UserID)

So using the WHERE NOT IN clause I can do:

SELECT DISTINCT(u.userID)
FROM Users u
WHERE u.userID NOT IN (SELECT o.userID FROM OldUsers o)

Performance is a key here also.

Upvotes: 0

Views: 136

Answers (3)

Thorsten
Thorsten

Reputation: 13181

You could also use a correlated subquery and EXISTS:

SELECT DISTINCT(u.userID)
FROM Users u
WHERE NOT EXISTS (
     SELECT o.userID 
     FROM OldUsers o 
     WHERE o.UserID = u.userID)

If this is actually faster depends on the size of the tables, so it's another version to try out as marc_s has suggested.

Upvotes: 0

marc_s
marc_s

Reputation: 754963

You could also use the EXCEPT keyword to do it this way:

SELECT UserID FROM Users
EXCEPT
SELECT UserID FROM OldUsers

As for performance: you'll just have to run the three scripts (your own with NOT IN, kragan's approach with LEFT JOIN and this one with EXCEPT) in comparison and measure for yourself what the speed is, how much resources they use etc. --> look at the execution plans, check out the IO statistics - the usual stuff...

Upvotes: 1

kragan
kragan

Reputation: 999

select distinct u.userID from Users u
left join OldUsers o on o.userId = u.userID
where o.userId is null

Upvotes: 3

Related Questions