Help Center/ GaussDB/ Distributed_8.x/ SQL Reference/ SQL Syntax/ S/ SELECT/ Compound Queries
Updated on 2024-06-03 GMT+08:00
View PDF

Compound Queries

A query that contains a compound operator is a compound query. All compound queries have the same priority. The number of columns and expressions in each query result in a set operation must be the same, and the types must be compatible.

Common set operations include:

  • UNION: a union set of the two query result sets where duplicates are removed.
  • UNION ALL: a union set of the two queries where the results of the two queries are simply combined.
  • INTERSECT: intersection of two queries (only duplicated data is displayed).
  • MINUS: difference of two query result sets. Only the data that exists in the first result set but does not exist in the second result set is displayed, and the data is sorted based on the results in the first column.

Examples

-- Create a table and insert data into the table.
gaussdb=# CREATE TABLE test1(c11 INT, c12 VARCHAR);
gaussdb=# INSERT INTO test1 VALUES (1,'a'),(2,'b'),(4,'d');

gaussdb=# CREATE TABLE test2(c21 INT, c22 VARCHAR); 
gaussdb=# INSERT INTO test2 VALUES (1,'a'),(3,'c');
  • UNION
    gaussdb=# SELECT * FROM test1 UNION SELECT * FROM test2;
 c11 | c12 
-----+-----
   1 | a
   4 | d
   2 | b
   3 | c
(4 rows)
  • UNION ALL
    gaussdb=# SELECT * FROM test1 UNION ALL SELECT * FROM test2;
 c11 | c12 
-----+-----
   1 | a
   2 | b
   4 | d
   1 | a
   3 | c
(5 rows)
  • INTERSECT
    gaussdb=# SELECT * FROM test1 INTERSECT SELECT * FROM test2;
 c11 | c12 
-----+-----
   1 | a
(1 row)
  • MINUS
    gaussdb=# SELECT * FROM test1 MINUS SELECT * FROM test2;
 c11 | c12 
-----+-----
   4 | d
   2 | b
(2 rows)
-- Delete.
gaussdb=# DROP TABLE test1,test2;
Parent topic: SELECT