(SQL) Replaced NOT IN with NOT EXISTS and results differ

Question

Trying to fix someone else's code. The NOT IN kills performance. I took it out and replaced with Not Exists and I'm getting different results. The commented out not in is just above my not exists. Anyone see anything stupid I'm doing here?

IF @ProcessComplete = 1
    BEGIN

--      PRINT 'Group-Complete'
    INSERT INTO @ProcessIDTable
    SELECT DISTINCT(ProcessID)
    FROM vPortalInbox
    WHERE  GroupUserIDs LIKE '%,' + CAST(@UserID AS VARCHAR(MAX)) + ',%' 
    AND StepOwnerID IS NULL

    --AND ProcessID NOT IN (SELECT ProcessID FROM @ProcessIDTable)

    And  not exists (SELECT ProcessID FROM @ProcessIDTable)

Those do two different things. In particular, your `NOT EXISTS()` does not involve the rows being `SELECT`ed. — SLaks, Nov 06 '13 at 22:33
You should really ask questions with titles that reflect the question you're trying to ask for help with. "I am doing something stupid" is not the question you're trying to ask. — neminem, Nov 06 '13 at 22:33
In this particular case you might even use except: `select ProcessID from ... except select ProcessID from @ProcessIDTable`. — Nikola Markovinović, Nov 06 '13 at 22:53

Monty Wild · Answer 1 · 2013-11-07T00:03:01.083

2

You could try:

And not exists (
  SELECT ProcessID
  FROM @ProcessIDTable
  WHERE ProcessID = vPortalInbox.ProcessID)

...or possibly even better still: you could try a (left or right) outer join to vPortalInbox on ProcessID, and specify in your WHERE clause that @ProcessIDTable.ProcessID IS NULL:

...
SELECT DISTINCT(ProcessID)
FROM vPortalInbox LEFT OUTER JOIN @ProcessIDTable 
     ON vPortalInbox.ProcessID = @ProcessIDTable.ProcessID 
WHERE  GroupUserIDs LIKE '%,' + CAST(@UserID AS VARCHAR(MAX)) + ',%' 
AND StepOwnerID IS NULL AND @ProcessIDTable.ProcessID IS NULL

edited Nov 07 '13 at 00:03

answered Nov 06 '13 at 22:45

Monty Wild

3,981
1
21
36

Yep, I'm an idiot. Completely forgot to add the where clause. Thanks so much. I think I looked at this for thirty minutes. – user2962554 Nov 07 '13 at 17:39

score 0 · Answer 2 · answered Nov 06 '13 at 22:58

1 --AND ProcessID NOT IN (SELECT ProcessID FROM @ProcessIDTable)

2 And  not exists (SELECT ProcessID FROM @ProcessIDTable)

The above two statements are not same. The not exist will evaluate to true only when you get no rows from the subquery inside the brackets following the not exists clause. Since you have no conditions for the sub-query following not exists clause, it will never return 0 rows unless the table is empty.

Try using this where clause:

FROM vPortalInbox P1
WHERE  GroupUserIDs LIKE '%,' + CAST(@UserID AS VARCHAR(MAX)) + ',%' 
AND StepOwnerID IS NULL

And  not exists (SELECT 1 FROM @ProcessIDTable P2
                 where  P1.ProcessID = P2.ProcessID  )

(SQL) Replaced NOT IN with NOT EXISTS and results differ

2 Answers2

Linked