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

Question

(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.

Answer 1

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

Answer 2

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...

Answer 3

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.