Обсуждение: [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
)

Ed Rouse <erouse@milner.com> writes:
> ... 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
(selectcontent 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.

I'm suspicious that this means there's at least one NULL in those content
columns.  That will cause the IN check to return either TRUE or NULL, not
FALSE.  You could recast to use EXISTS, or explicitly exclude nulls while
selecting from the attachments tables.
        regards, tom lane