Re: [SQL] Finding the negative

Version 9.1.

I have the following query that brings back the correct number of lobs still asscociated with data in other tables:

 

select count(distinct(loid)) from pg_largeobject where (loid in (select content from attachments) or loid in (select content from idw_form_workflow_attachments));

 

in that the loid’s in either table are found. What I need is to find the ones NOT in either table. I have tried using not in both with or/and  and various parenthesis. I tried:

 

select count(distinct(loid)) from pg_largeobject where not ((loid in (select content from attachments) or loid in (select content from idw_form_workflow_attachments)));

 

This should work since, if the loid is not in the content of either table, it should be F or F = F, negated to T;

which I would think would then count, but I get back 0 instead of the rather large number I should get. This is all a prelude to removing lobs that are no longer referenced.

 

Any ideas on how to get the inverse of the working query? Thanks

Isn't this a case for EXCEPT, the reverse of UNION? Like in:
SELECT COUNT(*) FROM
(
SELECT foo  FROM bar
EXCEPT
SELECT foo FROM cafe
)