SetOp

Description

A SetOp operator is used to combine two or more query results into a result set. SetOp operators include INTERSECT and EXCEPT.

Typical Scenarios

• INTERSECT: returns the intersection of two query results, that is, the rows that exist in both result sets.
• INTERSECT ALL: returns the intersection of two query results, including duplicate rows.
• EXCEPT: returns the rows that exist in the first query result but do not exist in the second query result.
• EXCEPT ALL: returns the rows that exist in the first query result but do not exist in the second query result, including duplicate rows.

Examples

Example 1: INTERSECT.

-- Prepare data.
gaussdb=# DROP TABLE IF EXISTS t;
gaussdb=# CREATE TABLE t(a int, b int, c int); 
CREATE TABLE 
gaussdb=# INSERT INTO t VALUES(generate_series(1, 10), generate_series(601, 610), generate_series(901, 910)); 
INSERT 0 10

-- Execution result.
gaussdb=# EXPLAIN SELECT * FROM t INTERSECT SELECT * FROM t; 
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=27.94..28.61 rows=21 width=12)
   Node/s: All datanodes
   ->  SetOp Intersect  (cost=27.13..27.39 rows=21 width=12)
         ->  Sort  (cost=27.13..27.20 rows=42 width=12)
               Sort Key: "*SELECT* 1".a, "*SELECT* 1".b, "*SELECT* 1".c
               ->  Append  (cost=0.00..26.52 rows=42 width=12)
                     ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..13.26 rows=21 width=12)
                           ->  Seq Scan on t  (cost=0.00..13.13 rows=20 width=12)
                     ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..13.26 rows=21 width=12)
                           ->  Seq Scan on t  (cost=0.00..13.13 rows=20 width=12)
(10 rows)

In the preceding example, the output of the SetOp operator is as follows.

Example 2: INTERSECT ALL.

gaussdb=# EXPLAIN SELECT * FROM t INTERSECT ALL SELECT * FROM t; 
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=27.94..28.61 rows=21 width=12)
   Node/s: All datanodes
   ->  SetOp Intersect All  (cost=27.13..27.39 rows=21 width=12)
         ->  Sort  (cost=27.13..27.20 rows=42 width=12)
               Sort Key: "*SELECT* 1".a, "*SELECT* 1".b, "*SELECT* 1".c
               ->  Append  (cost=0.00..26.52 rows=42 width=12)
                     ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..13.26 rows=21 width=12)
                           ->  Seq Scan on t  (cost=0.00..13.13 rows=20 width=12)
                     ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..13.26 rows=21 width=12)
                           ->  Seq Scan on t  (cost=0.00..13.13 rows=20 width=12)
(10 rows)

In the preceding example, the output of the SetOp operator is as follows.

Example 3: EXCEPT.

gaussdb=# EXPLAIN SELECT * FROM t EXCEPT SELECT * FROM t; 
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=27.94..28.61 rows=21 width=12)
   Node/s: All datanodes
   ->  SetOp Except  (cost=27.13..27.39 rows=21 width=12)
         ->  Sort  (cost=27.13..27.20 rows=42 width=12)
               Sort Key: "*SELECT* 1".a, "*SELECT* 1".b, "*SELECT* 1".c
               ->  Append  (cost=0.00..26.52 rows=42 width=12)
                     ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..13.26 rows=21 width=12)
                           ->  Seq Scan on t  (cost=0.00..13.13 rows=20 width=12)
                     ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..13.26 rows=21 width=12)
                           ->  Seq Scan on t  (cost=0.00..13.13 rows=20 width=12)
(10 rows)

In the preceding example, the output of the SetOp operator is as follows.

Example 4: EXCEPT ALL.

 
gaussdb=# EXPLAIN SELECT * FROM t EXCEPT ALL SELECT * FROM t; 
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=27.94..28.61 rows=21 width=12)
   Node/s: All datanodes
   ->  SetOp Except All  (cost=27.13..27.39 rows=21 width=12)
         ->  Sort  (cost=27.13..27.20 rows=42 width=12)
               Sort Key: "*SELECT* 1".a, "*SELECT* 1".b, "*SELECT* 1".c
               ->  Append  (cost=0.00..26.52 rows=42 width=12)
                     ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..13.26 rows=21 width=12)
                           ->  Seq Scan on t  (cost=0.00..13.13 rows=20 width=12)
                     ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..13.26 rows=21 width=12)
                           ->  Seq Scan on t  (cost=0.00..13.13 rows=20 width=12)
(10 rows)

-- Drop.
gaussdb=# DROP TABLE t;

In the preceding example, the output of the SetOp operator is as follows.