Hi,
According to the following report, I think using "except" would be the best
way to do. Thank you!
Jack
========================
EXPLAIN
SELECT DISTINCT a.c1
FROM test_j2 a
WHERE a.c1 NOT IN (SELECT DISTINCT b.c1 FROM test_j1 b);
Unique (cost=54544.91..54547.41 rows=50 width=6) -> Sort (cost=54544.91..54546.16 rows=500 width=6) Sort
Key:c1 -> Seq Scan on test_j2 a (cost=0.00..54522.50 rows=500 width=6) Filter: (subplan)
SubPlan -> Materialize (cost=54.50..54.50 rows=100 width=6) -> Unique
(cost=0.00..54.50rows=100 width=6) -> Index Scan using test_j1_pkey on test_j1 b
(cost=0.00..52.00 rows=1000 width=6)
(9 rows)
=======================
EXPLAIN
SELECT a.c1 FROM test_j2 a
EXCEPT
SELECT b.c1 FROM test_j1 b;
SetOp Except (cost=149.66..159.66 rows=200 width=6) -> Sort (cost=149.66..154.66 rows=2000 width=6) Sort
Key:c1 -> Append (cost=0.00..40.00 rows=2000 width=6) -> Subquery Scan "*SELECT* 1"
(cost=0.00..20.00rows=1000
width=6) -> Seq Scan on test_j2 a (cost=0.00..20.00 rows=1000
width=6) -> Subquery Scan "*SELECT* 2" (cost=0.00..20.00 rows=1000
width=6) -> Seq Scan on test_j1 b (cost=0.00..20.00 rows=1000
width=6)
(8 rows)
=========================
EXPLAIN
SELECT DISTINCT a.c1
FROM test_j2 a
WHERE NOT EXISTS
(SELECT b.c1 FROM test_j1 b WHERE b.c1 = a.c1) ;
Unique (cost=3455.91..3458.41 rows=50 width=6) -> Sort (cost=3455.91..3457.16 rows=500 width=6) Sort Key: c1
-> Seq Scan on test_j2 a (cost=0.00..3433.50 rows=500 width=6) Filter: (NOT (subplan))
SubPlan -> Index Scan using test_j1_pkey on test_j1 b
(cost=0.00..17.07 rows=5 width=6) Index Cond: (c1 = $0)
(8 rows)
