SQL NOT EXISTS not returning expected results

Question

Using SSRS 2008 R2

I'm having some problems working out how to look for records that are in one query and not another.

I've tried NOT EXISTS, NOT IN and LEFT JOIN.

When I try the sub query on its own it works fine, but when I join it to the first query with NOT EXISTS it returns no records (there should be around 5000 records returned).

I found this post SQL "select where not in subquery" returns no results and whilst I'm not sure I understand all of the answers (!) I did try filtering out any nulls that either query returns.

FYI it's not enough just to look for people who are in the first query where document type is not = 4 as I am doing something more complicated with additional columns I need for a table - I thought it would be easier for someone to point me in the right direction if I distilled the query down to that shown below.

Thanks, Eileen

SELECT
  TblPeople.PeopleId
  ,TblPeople.FirstName
  ,TblPeople.Surname

FROM 
TblPeople

WHERE TblPeople.PeopleId IS NOT NULL
AND NOT EXISTS 

(SELECT
  TblPeople.PeopleId 

FROM
  TblPeople
  INNER JOIN TblDocument 
    ON TblDocument.DocumentPeopleId = TblPeople.PeopleId

WHERE
  TblDocument.DocumentType = 4 
  AND TblPeople.PeopleId IS NOT NULL)

Thanks for responding. I've just tried that with the following code but it doesn't seem to make a difference — EileenS, Oct 06 '15 at 09:22
' SELECT p.PeopleId ,p.FirstName ,p.Surname FROM TblPeople AS p WHERE p.PeopleId IS NOT NULL AND NOT EXISTS (SELECT TblPeople.PeopleId FROM TblPeople INNER JOIN TblDocument ON TblDocument.DocumentPeopleId = TblPeople.PeopleId WHERE TblDocument.DocumentType = 4 AND TblPeople.PeopleId IS NOT NULL) ' — EileenS, Oct 06 '15 at 09:22

Pred · Accepted Answer · 2015-10-06T10:47:15.897

The problems with your query:

You did no link the subquery to the main query
The TblPeople is not necessary in the subquery (since you can link the TblDocuments table using a simple where condition)

The fixed query looks like this:

SELECT
  TblPeople.PeopleId
  ,TblPeople.FirstName
  ,TblPeople.Surname
FROM 
  TblPeople
WHERE
  TblPeople.PeopleId IS NOT NULL -- This is probably not necessary (assuming, PeopleId is the primary key, therefore it is NOT NULL).
  AND NOT EXISTS (
    SELECT
      1 -- does not really matter what is the column to retrieve
    FROM
      TblDocument AS D
    WHERE
      TblDocument.DocumentPeopleId = TblPeople.PeopleId
      AND TblDocument.DocumentType = 4
  )

The above query will list all people who has no DocumentType=4 documents.

When I join it to the first query with NOT EXISTS it returns no records (there should be around 5000 records returned).

This is because you did not have a link between the records in the main query and the subquery. (There are records with DocumentType = 4, and the PeopleId is not defined, so any of the will be fine -> the NOT EXISTS will say FALSE for all records).

Semantically, the subquery in the [NOT] EXISTS evaluated row by row for each record in the main query's recordset.

Thank you for your answer! With a bit of tweaking I was able to make this work for my full list of columns :) — EileenS, Oct 06 '15 at 11:52

score 0 · Answer 2 · answered Oct 06 '15 at 09:21

0

I think u can change it a bit to use except:

      SELECT
      TblPeople.PeopleId
      ,TblPeople.FirstName
      ,TblPeople.Surname

    FROM 
    TblPeople

    WHERE TblPeople.PeopleId IS NOT NULL

    EXCEPT

    SELECT
      TblPeople.PeopleId 

    FROM
      TblPeople
      INNER JOIN TblDocument 
        ON TblDocument.DocumentPeopleId = TblPeople.PeopleId

    WHERE
      TblDocument.DocumentType = 4 
      AND TblPeople.PeopleId IS NOT NULL

answered Oct 06 '15 at 09:21

ProblemSolver

636
1
8
16

Thanks for your answer - I had some trouble with EXCEPT because the full query I want to use don't have exactly matching fields - I can see that this isn't obvious from the distilled version I posted – EileenS Oct 06 '15 at 11:51

SQL NOT EXISTS not returning expected results

2 Answers2