Delete All Records In Table Which Have No Reference In Another Table

I have a table which is called Document. Document: id int docuid int doc blob Then I have two referencing tables AppRequiredDocuments: id int appid int docid int -> references

Solution 1:

One approach uses a delete join:

DELETE d
FROM Document d
LEFTJOIN AppRequiredDocuments t1
  ON d.id = t1.docid
LEFTJOIN AppDocuments t2
  ON d.id = t2.docid
WHERE t1.docid ISNULLAND
      t2.docid ISNULL

The logic here is that if a given Document record is not referenced by anything in the two auxiliary tables, then in the result set of the join the docid columns for those two other tables should both be NULL.

Solution 2:

You could use the union [all] operator to generate a single column of references, and then check against it, e.g., with the [not] exists operator:

DELETEFROM Document d
WHERENOTEXISTS (SELECT*FROM   AppRequiredDocuments ard
                   WHERE  ard.docid = d.id
                   UNIONALLSELECT*FROM   AppDocuments ad
                   WHERE  ad.docid = d.id)

Solution 3:

You can use NOT EXISTS to find and delete those items:

deletefrom document d
wherenotexists (select1from AppRequiredDocuments a where a.docid = d.id);
andnotexists (select1from AppDocuments a where a.docid = d.id);