SQL "Where Not Exists" is not returning any rows

Question

Here is what I am trying to accomplish: 1. Pull user ID from the users table (documents.ownerID is foreign key of Users.Handle) 2. For users who do NOT have a record that was created in the documents table within the last 90 days

Here is the query that I have so far (I am running this through SQL Studio Management 2012):

Select Users.UserID 
From Documents  
Inner Join Users on documents.OwnerID = users.handle
Where Not Exists
(
Select * 
From Documents
Where documents.creationtime >= Dateadd(day, -90, getutcdate())
)
Group by Users.UserID
Order by Users.UserID ASC

Nothing is returned in the output. However, when I remove the "Not" from the "Not Exists" statement I get an output of users who have a record that was created in the documents table within the last 90 days. Also, if I change the ">=" indicator to "=" I receive output as well. I think the problem is that I don't have a firm understanding of the EXIST statement. I greatly appreciate your help.

Answer 1

The reason is because your subquery is not correlated. So, the where clause is saying: where no record exists in documents with a creation time greater than 90 days. There are such records, so the where clause always fails.

My guess is that you want this by user. If so, then try this where clause:

where not exists (select *
                  from documents d2
                  where d2.creationtime >= Dateadd(day, -90, getutcdate()) and
                        d2.OwnerId = users.handle
                 )

Answer 2

One way is to put the Documents table inside the subquery only, removing it from the external query, so making the subquery a correlated one:

SELECT Users.UserID 
FROM Users 
WHERE NOT EXISTS
    (
    SELECT * 
    FROM Documents
    WHERE Documents.OwnerID = Users.handle
      AND Documents.creationtime >= Dateadd(day, -90, getutcdate())
    ) ;

This way, you can also get rid of the GROUP BY for free.