X-Original-To: pgsql-sql@postgresql.org
Received: from mail1.tpgi.com.au (mail.tpgi.com.au [203.12.160.57])
	by postgresql.org (Postfix) with ESMTP id C2D9C474E4F
	for <pgsql-sql@postgresql.org>; Tue, 18 Mar 2003 06:17:04 -0500 (EST)
Received: from jac (bri-ts4-2600-107.tpgi.com.au [203.219.27.107])
	by mail1.tpgi.com.au (8.11.6/8.11.6) with SMTP id h2IBH5d23018
	for <pgsql-sql@postgresql.org>; Tue, 18 Mar 2003 22:17:05 +1100
Message-ID: <002c01c2ed3f$e7fee740$1400a8c0@jac>
From: "jack" <datactrl@tpg.com.au>
To: <pgsql-sql@postgresql.org>
Subject: the best way to get some records not in another table
Date: Tue, 18 Mar 2003 21:17:03 +1000
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Archive-Number: 200303/429
X-Sequence-Number: 12468

Hi, all

Try to get some records not in another table. As the following, please
advise which one will be the best way to do. Or is there any other way to do
better?

SELECT DISTINCT a.c1
FROM test_j2 a
WHERE a.c1 NOT IN (SELECT DISTINCT b.c1 FROM test_j1 b);

SELECT a.c1 FROM test_j2 a
EXCEPT
SELECT b.c1 FROM test_j1 b;


Jack