CODEWITHSUNDEEP
sqlsql-server-2008
Leron
Leron

Reputation: 9866

NOT EXISTS with INNER JOIN not returning the expected result

I have temporary table #Inserts where I collect records on which eventually I will perform INSERT at some point. Before doing that I need to filter out some of the data. The first query is using NOT EXISTS and it doesn't return the correct results:

  SELECT I.UnitId 
  FROM #Inserts AS I 
  WHERE NOT EXISTS (
    SELECT 1  
    from   #Inserts AS INS  
    INNER JOIN Organisation AS O 
        ON INS.OrganisationId = O.OrganisationId
        AND INS.UnitId = O.UnitId
    )

However if do the following:

  DELETE INS FROM #Inserts AS INS   
  INNER JOIN Organisation AS O
        ON INS.OrganisationId = O.OrganisationId
        AND INS.UnitId = O.UnitId

then if i SELECT * FROM #Inserts I can see the the records are filtered as I've been expected. What am I doing wrong with the NOT EXISTS and how can I get the same behavior as that with DELETE?

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I believe you intend a correlated subquery:

SELECT I.UnitId 
FROM #Inserts I 
WHERE NOT EXISTS (SELECT 1  
                  FROM Organisation AS O 
                  WHERE I.OrganisationId = O.OrganisationId AND I.UnitId = O.UnitId
                 );

Your version is running an independent subquery. If any rows are returned, then NOT EXISTS returns false.

Upvotes: 2

Related Questions