(SQL)将NOT IN替换为NOT EXISTS，结果有所不同 [英] (SQL) Replaced NOT IN with NOT EXISTS and results differ

问题描述

试图修复别人的代码. NOT IN会降低性能. 我将其取出并替换为不存在"，并且得到了不同的结果. 评论出来的不在上方只是我不存在的上方. 有人看到我在这里做什么愚蠢吗?

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)

推荐答案

您可以尝试:

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

...甚至可能更好:您可以尝试对ProcessID上的vPortalInbox进行(左或右)外部联接，并在WHERE子句中指定@ProcessIDTable.ProcessID IS NULL:

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

这篇关于(SQL)将NOT IN替换为NOT EXISTS，结果有所不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！