Received: from earth.hub.org (earth.hub.org [64.49.215.11]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1GLsiE29019 for ; Sat, 16 Feb 2002 16:54:44 -0500 (EST) (envelope-from news@jupiter.hub.org) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g1GLYg360928 for pgsql-sql@postgresql.org; Sat, 16 Feb 2002 15:34:42 -0600 (CST) (envelope-from news) From: Mark Nielsen X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Join Statements Date: Sat, 16 Feb 2002 13:30:52 -0500 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 32 Message-ID: <3C6EA55C.8040905@kepnet.net> References: <5e0d3eda.0202110715.764ff25f@posting.google.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: news@hub.org User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.5) Gecko/20011012 X-Accept-Language: en-us To: pgsql-sql@postgresql.org X-Archive-Number: 200202/203 X-Sequence-Number: 6628 select distinct TABLENAME_id from TABLENAME_backup where NOT TABLENAME_id = ANY (select TABLENAME_id from TABLENAME) Here is an example of me getting all the ids from the backup table that doesn't exist in the main table. This is similar to what you want. I think I am answering your question, or close to it. Mark T Conti wrote: > Howdy: > > I need to put together an SQL statement that will return all of the > rows in table A that are not in table B. Table A and B have the same > primary key. For example: > > select count(a.*) > from a (nolock) left outer join > b (nolock) on a.id = b.id > where a.id != b.id > > This did not work. It returned 0 rows. I know that this could be > done very easily in a sub-select, but that seems inefficient. Is > there any way to accomplish what I mentioned above in the join > statement or is the sub-select the way to go? > > Thanks for the help, > Tom >