From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ed Rouse <erouse@milner.com>
cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: Finding the negative
In-reply-to: 
 <DE8D456CF535514BB21272D05C4A1C391E66AB2E@mbx029-e1-va-10.exch029.domain.local>
References: 
 <DE8D456CF535514BB21272D05C4A1C391E66AB2E@mbx029-e1-va-10.exch029.domain.local>
Comments: In-reply-to Ed Rouse <erouse@milner.com>
	message dated "Thu, 06 Jul 2017 19:13:09 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <26801.1499370203.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 06 Jul 2017 15:43:23 -0400
Message-ID: <26802.1499370203@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Ed Rouse <erouse@milner.com> writes:
> ... What I need is to find the ones NOT in either table. I have tried usi=
ng not in both with or/and  and various parenthesis. I tried:

> select count(distinct(loid)) from pg_largeobject where not ((loid in (sel=
ect content from attachments) or loid in (select content from idw_form_work=
flow_attachments)));

> This should work since, if the loid is not in the content of either table=
, it should be F or F =3D F, negated to T;
> which I would think would then count, but I get back 0 instead of the rat=
her 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


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql