阿西河

所有教程

公众号
🌙
阿西河前端的公众号

我的收藏

    最近访问  (文章)

      教程列表

      抓包专区
      测试专区

      PostgreSQL union

      PostgreSQL UNION 操作符

      PostgreSQL UNION 操作符合并两个或多个 SELECT 语句的结果。

      UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

      请注意,UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。

      语法

      UNIONS 基础语法如下:

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

      这里的条件语句可以根据您的需要设置任何表达式。

      实例

      创建 COMPANY 表,数据内容如下:

      axihedb# 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)
      

      创建 DEPARTMENT 表,数据内容如下:

      axihedb=# 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 子句将两张表连接起来,如下所示:

      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。

      语法

      UINON ALL 子句基础语法如下:

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

      这里的条件语句可以根据您的需要设置任何表达式。

      实例

      现在,让我们把上面提到的两张表用 SELECT 语句结合 UNION ALL 子句连接起来:

      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)
      
      目录
      目录