PostgreSQL - UNIONS 子句

• 

  简述

  PostgreSQLUNION子句/运算符用于组合两个或多个 SELECT 语句的结果，而不返回任何重复的行。

  要使用 UNION，每个 SELECT 必须选择相同数量的列、相同数量的列表达式、相同的数据类型，并且它们的顺序相同，但它们的长度不必相同。
• 

  句法

  的基本语法UNION如下 -

  
  SELECT column1 [, column2 ]
  FROM table1 [, table2 ]
  [WHERE condition]
  UNION
  SELECT column1 [, column2 ]
  FROM table1 [, table2 ]
  [WHERE condition]
  

  在这里，给定条件可以是根据您的要求的任何给定表达式。
• 

  例子

  考虑以下两个表，（a） COMPANY 表如下 -

  
  testdb=# SELECT * from COMPANY;
   id | name  | age | address   | salary
  ----+-------+-----+-----------+--------
    1 | Paul  |  32 | California|  20000
    2 | Allen |  25 | Texas     |  15000
    3 | Teddy |  23 | Norway    |  20000
    4 | Mark  |  25 | Rich-Mond |  65000
    5 | David |  27 | Texas     |  85000
    6 | Kim   |  22 | South-Hall|  45000
    7 | James |  24 | Houston   |  10000
  (7 rows)
  

  (b) 另一个表是 DEPARTMENT 如下 -

  
  testdb=# SELECT * from DEPARTMENT;
   id | dept        | emp_id
  ----+-------------+--------
    1 | IT Billing  |      1
    2 | Engineering |      2
    3 | Finance     |      7
    4 | Engineering |      3
    5 | Finance     |      4
    6 | Engineering |      5
    7 | Finance     |      6
  (7 rows)
  

  现在让我们使用 SELECT 语句和 UNION 子句连接这两个表，如下所示 -

  
  testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
     ON COMPANY.ID = DEPARTMENT.EMP_ID
     UNION
        SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
           ON COMPANY.ID = DEPARTMENT.EMP_ID;
  

  这将产生以下结果 -

  
   emp_id | name  |  dept
  --------+-------+--------------
        5 | David | Engineering
        6 | Kim   | Finance
        2 | Allen | Engineering
        3 | Teddy | Engineering
        4 | Mark  | Finance
        1 | Paul  | IT Billing
        7 | James | Finance
  (7 rows)
  

• 

  UNION ALL 子句

  UNION ALL 运算符用于组合两个 SELECT 语句（包括重复行）的结果。适用于 UNION 的相同规则也适用于 UNION ALL 运算符。

  句法

  的基本语法UNION ALL如下 -

  
  SELECT column1 [, column2 ]
  FROM table1 [, table2 ]
  [WHERE condition]
  UNION ALL
  SELECT column1 [, column2 ]
  FROM table1 [, table2 ]
  [WHERE condition]
  

  在这里，给定条件可以是根据您的要求的任何给定表达式。

  例子

  现在，让我们在 SELECT 语句中加入上述两个表，如下所示 -

  
  testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
     ON COMPANY.ID = DEPARTMENT.EMP_ID
     UNION ALL
        SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
           ON COMPANY.ID = DEPARTMENT.EMP_ID;
  

  这将产生以下结果 -

  
   emp_id | name  | dept
  --------+-------+--------------
        1 | Paul  | IT Billing
        2 | Allen | Engineering
        7 | James | Finance
        3 | Teddy | Engineering
        4 | Mark  | Finance
        5 | David | Engineering
        6 | Kim   | Finance
        1 | Paul  | IT Billing
        2 | Allen | Engineering
        7 | James | Finance
        3 | Teddy | Engineering
        4 | Mark  | Finance
        5 | David | Engineering
        6 | Kim   | Finance
  (14 rows)