September 19, 2019 at 11:00 am
I have been tasked with identifying a set of users in one of our databases. The following code should be reasonably self-explanatory:
--There are 1,360 Users
SELECT COUNT(*) AS NumUsers FROM Users;
-- Use a CTE to identify all Users who are linked to Actions - there are 82 of these
;WITH ActionCentreUsers AS
(
SELECT DISTINCT UserID AS UserId FROM ActionPlans
UNION
SELECT DISTINCT CreatorId AS UserId FROM Actions
UNION
SELECT DISTINCT AssignorId AS UserId FROM Actions
UNION
SELECT DISTINCT ModifiedBy AS UserId FROM Actions
)
--This JOIN returns 82 rows
SELECT * FROM Users INNER JOIN ActionCentreUsers ON Users.UserId = ActionCentreUsers.UserId
-- As a result of the above, I would expect this to return 1,360 - 82 = 1278. It actually returns 0 rows
SELECT * FROM Users WHERE UserId NOT IN (SELECT UserId FROM ActionCentreUsers)
-- This, on the other hand, returns 1,278 rows
SELECT * FROM Users LEFT JOIN ActionCentreUsers ON Users.UserId = ActionCentreUsers.UserId WHERE ActionCentreUsers.UserId IS NULL
I realise that a CTE falls out of scope once used, so in testing I've commented out the successive SELECT statements. My question is, why does this statement return 0 rows?
SELECT * FROM Users WHERE UserId NOT IN (SELECT UserId FROM ActionCentreUsers
Many thanks
Edward
September 19, 2019 at 12:10 pm
I bet at least one of the values of UserID in your CTE ActionCentreUsers has the value NULL. If so, that is documented behaviour:
Caution
Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results.
Try using NOT EXISTS instead.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 19, 2019 at 12:12 pm
Ah thanks Thom. I've already replaced the CTE with a table variable (as it turns out I need to reference it multiple times in a much more detailed batch) and the NULL value bit me. So that's good to know!
September 19, 2019 at 12:38 pm
If there were any need to persist this, then I'd agree. However, the script is merely to identify a set of users for later deletion. The circumstances which have given rise to this requirement will not recur, and so there's no need for this to exist beyond today.
September 19, 2019 at 2:32 pm
Also, get rid of the DISTINCT. UNION already does a distinct, so specifying DISTINCT here just clutters up your code with no benefit.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 19, 2019 at 3:01 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply