Except

名称

差集算子(去重)

介绍

差集算子返回所有在左查询的结果集不在右查询结果集的去重数据行。

举例

--清理环境
DROP TABLE t1 CASCADE;
DROP TABLE t2 CASCADE;

--创建环境
CREATE TABLE t1(a int);
INSERT INTO t1 SELECT generate_series(1,10);
ANALYZE t1;

CREATE TABLE t2(a int);
INSERT INTO t2 SELECT generate_series(1,5);
ANALYZE t2;

EXPLAIN ANALYZE SELECT * FROM t1 EXCEPT SELECT * FROM t2;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 SetOp Except  (cost=14.44..14.52 rows=2 width=4) (actual time=0.07..0.08 rows=5 loops=1)
   ->  Sort  (cost=14.44..14.48 rows=15 width=4) (actual time=0.06..0.06 rows=15 loops=1)
         Sort Key: A
         ->  Append  (cost=0.00..14.15 rows=15 width=4) (actual time=0.02..0.04 rows=15 loops=1)
               ->  Subquery Scan "*SELECT* 1"  (cost=0.00..7.10 rows=10 width=4) (actual time=0.02..0.02 rows=10 loops=1)
                     ->  Seq Scan on T1  (cost=0.00..7.10 rows=10 width=4) (actual time=0.01..0.02 rows=10 loops=1)
               ->  Subquery Scan "*SELECT* 2"  (cost=0.00..7.05 rows=5 width=4) (actual time=0.01..0.01 rows=5 loops=1)
                     ->  Seq Scan on T2  (cost=0.00..7.05 rows=5 width=4) (actual time=0.01..0.01 rows=5 loops=1)
 Planning Time: 0.40 msec
 Execution Time: 0.50 msec
(10 rows)