复合查询

UNION

常见的集合运算有：

• UNION：两个查询结果集的并集，对结果进行去重。
• UNION ALL：两个查询的并集，只将两个查询的结果合并。

UNION示例

-- 建表并插入数据
m_db=# CREATE TABLE test1(c11 INT, c12 VARCHAR(20));
m_db=# INSERT INTO test1 VALUES (1,'a'),(2,'b'),(4,'d');

m_db=# CREATE TABLE test2(c21 INT, c22 VARCHAR(20)); 
m_db=# INSERT INTO test2 VALUES (1,'a'),(3,'c');

• UNION

  m_db=# SELECT * FROM test1 UNION SELECT * FROM test2;
   c11 | c12 
  -----+-----
     4 | d
     3 | c
     1 | a
     2 | b
  (4 rows)

• UNION ALL

  m_db=# SELECT * FROM test1 UNION ALL SELECT * FROM test2;
   c11 | c12 
  -----+-----
     1 | a
     2 | b
     4 | d
     1 | a
     3 | c
  (5 rows)

EXCEPT

常见的交集运算有：

• EXCEPT：在第一个查询结果中去除两个查询结果的交集，并对结果进行去重。
• EXCEPT ALL：在第一个查询结果中去除两个查询结果的交集，不进行去重。
• EXCEPT DISTINCT：在第一个查询结果中去除两个查询结果的交集，并对结果进行去重。

EXCEPT示例

-- 建表并插入数据
m_db=# CREATE TABLE a(m int,n int);
m_db=# INSERT INTO a(m,n) VALUES(1,2);
m_db=# INSERT INTO a(m,n) VALUES(2,3);
m_db=# INSERT INTO a(m,n) VALUES(3,4);

m_db=# CREATE TABLE b(m int,n int);
m_db=# INSERT INTO b(m,n) VALUES(1,2);
m_db=# INSERT INTO b(m,n) VALUES(1,3);
m_db=# INSERT INTO b(m,n) VALUES(3,4);

m_db=# CREATE TABLE c(m int,n int);
m_db=# INSERT INTO c(m,n) VALUES(1,3);
m_db=# INSERT INTO c(m,n) VALUES(1,3);
m_db=# INSERT INTO c(m,n) VALUES(3,4);

• EXCEPT

  m_db=# SELECT * FROM a EXCEPT SELECT * FROM b;
   m | n
  ---+---
   2 | 3
  (1 row)
  
  m_db=# SELECT * FROM a EXCEPT SELECT * FROM c;
   m | n
  ---+---
   2 | 3
   1 | 2
  (2 rows)
  
  m_db=# SELECT * FROM b EXCEPT SELECT * FROM a;
   m | n
  ---+---
   1 | 3
  (1 row)
  -- 对查询结果进行去重。
  m_db=# SELECT * FROM c EXCEPT SELECT * FROM a;
   m | n
  ---+---
   1 | 3
  (1 row)

• EXCEPT ALL

  m_db=# SELECT * FROM a EXCEPT ALL SELECT * FROM b;
   m | n
  ---+---
   2 | 3
  (1 row)
  
  m_db=# SELECT * FROM a EXCEPT ALL SELECT * FROM c;
   m | n
  ---+---
   2 | 3
   1 | 2
  (2 rows)
  
  m_db=# SELECT * FROM b EXCEPT ALL SELECT * FROM a;
   m | n
  ---+---
   1 | 3
  (1 row)
  
  m_db=# SELECT * FROM c EXCEPT ALL SELECT * FROM a;
   m | n
  ---+---
   1 | 3
   1 | 3
  (2 rows)

• EXCEPT DISTINCT

  m_db=# SELECT * FROM a EXCEPT DISTINCT SELECT * FROM b;
   m | n
  ---+---
   2 | 3
  (1 row)
  
  m_db=# SELECT * FROM a EXCEPT DISTINCT SELECT * FROM c;
   m | n
  ---+---
   2 | 3
   1 | 2
  (2 rows)
  
  m_db=# SELECT * FROM b EXCEPT DISTINCT SELECT * FROM a;
   m | n
  ---+---
   1 | 3
  (1 row)
  -- 对查询结果进行去重。
  m_db=# SELECT * FROM c EXCEPT DISTINCT SELECT * FROM a;
   m | n
  ---+---
   1 | 3
  (1 row)
  
  m_db=# DROP TABLE a,b,c;