2

I've been using this query statement ever since. I wonder why this does not work on SQL Server 2008 R2.

SELECT
    UserName
FROM 
    Users 
WHERE 
    UserName NOT IN (SELECT UserName FROM UserTableT2)

The codes does not return any data. Goal is select all UserName in Users table which do not belong to UserTableT2.

EDIT:

Here's the actual query enter image description here

Update using @Tim Schelmter's query: enter image description here

Update : enter image description here

Update: enter image description here Thank you!

n00bster
  • 41
  • 1
  • 8
  • 5
    You probably have `null` values in `UserTableT2.UserName`. I'm pretty sure this is a duplicate - this is getting asked very often, but I'm too lazy to search for it now –  Nov 02 '15 at 08:54
  • Try this - `SELECT UserName FROM Users WHERE UserName NOT IN (SELECT UserName FROM UserTableT2 WHERE UserName IS NOT NULL)` – Abhishek Nov 02 '15 at 08:57
  • Hi @a_horse_with_no_name there's no NULL values in any field in both tables. – n00bster Nov 02 '15 at 09:27
  • Updated with the actual data. – n00bster Nov 02 '15 at 09:32
  • Looks strange, is the collation of the fields same in both tables? – James Z Nov 02 '15 at 18:25

1 Answers1

4

I would use NOT EXISTS:

SELECT u.UserName
FROM Users u
WHERE NOT EXISTS
(
   SELECT 1 FROM UserTableT2 ut2
   WHERE u.UserName = ut2.UserName 
)

Why? Because it works also if there are NULL values in UserTableT2.UserName.

Worth reading:

Instead of NOT IN, use a correlated NOT EXISTS for this query pattern. Always. Other methods may rival it in terms of performance, when all other variables are the same, but all of the other methods introduce either performance problems or other challenges.

With your updated columns and tables:

SELECT u.usr_id
FROM ousr u
WHERE NOT EXISTS
(
   SELECT 1 FROM ApprovalStageApprovers asa
   WHERE u.usr_id = asa.ApprovalUser
)
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Hi, I've tried using NOT Exists too, but same result happens. It returns zero result. – n00bster Nov 02 '15 at 09:28
  • @n00bster: then we cant help further since we don't have sample data that demonstrates the issue. Maybe there are some [invisible characters](http://stackoverflow.com/questions/8655909/whats-the-best-way-to-identify-hidden-characters-in-the-result-of-a-query-in-sq). – Tim Schmelter Nov 02 '15 at 09:29
  • Hi @Tim Schmelter updated the question, attached the actual data. – n00bster Nov 02 '15 at 09:33
  • @n00bster: both queries don't make much sense to me. Is `usr_id` really storing the same values as `ApprovalStageApprovers.ApprovalUser`? But even if, you are not using a correlated subquery. The `EXISTS` is not a `NOT EXISTS` and both are not related to the main query. You also haven't provided sample data that demonstrated the issue but only the result. – Tim Schmelter Nov 02 '15 at 09:37
  • updated the question. Sorry for not including the actual table contents. – n00bster Nov 02 '15 at 09:45
  • @n00bster: why don't you use my query? I can't only repeat myself, you have to link the subquery with the main query. Where's the `WHERE` clause in the `NOT EXISTS`? – Tim Schmelter Nov 02 '15 at 09:46
  • I have tried using your query but it returns the same result. User 'kris' should not be in the list since it exists in the ApprovalStageApprovers table. Please see updated screenshot above. – n00bster Nov 02 '15 at 09:49
  • @n00bster: now i have edited my answer to provide the query with your updated columns and tables. Have you verified that there are no invisible characters, f.e. leading or trailing spaces? Is the length the same? `SELECT DATALENGTH(ApprovalUser)FROM ApprovalStageApprovers` – Tim Schmelter Nov 02 '15 at 09:50
  • Hi @Tim Schmelter updated with data length posted. Same length. :( – n00bster Nov 02 '15 at 10:00