CODEWITHSUNDEEP
sql-serverselectwhere-clause
n00bster
n00bster

Reputation: 41

SQL Server : using SELECT in NOT IN WHERE Clause

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!

Upvotes: 2

Views: 8560

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460360

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
)

Upvotes: 4

Related Questions