PgSQL——学习笔记12：UNION 操作符 & UNION ALL 操作符

目录

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

UNION ALL 操作符：可以连接两个有重复行的 SELECT 语句

---

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

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

请注意：

• UNION 内部的每个 SELECT 语句必须拥有相同数量的列。
• 另外，每个 SELECT 语句中的列也必须拥有相似的数据类型。
• 同时，每个 SELECT 语句中的列的顺序必须相同。

/*UNIONS 基础语法如下：*/
/*这里的条件语句可以根据您的需要设置任何表达式。*/

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

UNION

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

实例：

mydb=# delete from department where id = 4;
DELETE 1
mydb=# select * from department;
 id |                        dept                        | emp_id
----+----------------------------------------------------+--------
  1 | IT Billing                                         |      1
  2 | Engineering                                        |      2
  3 | Finance                                            |      7
(3 行记录)

mydb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (4,'Engineering',3),(5,'Finance',4),(6,'Engineering',5),(7,'Finance',6);
INSERT 0 4
mydb=# 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 行记录)

mydb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
mydb-#    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
(7 行记录)

mydb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
mydb-#     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
        | James |
        | Paul  |
        | James |
(10 行记录)

/*在 SELECT 语句中使用 UNION 子句将两张表连接起来，如下所示：*/
mydb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
mydb-#    ON COMPANY.ID = DEPARTMENT.EMP_ID
mydb-#    UNION
mydb-# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
mydb-#     ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |                        dept
--------+-------+----------------------------------------------------
      2 | Allen | Engineering
        | Paul  |
      6 | Kim   | Finance
      1 | Paul  | IT Billing
      5 | David | Engineering
      4 | Mark  | Finance
      3 | Teddy | Engineering
        | James |
      7 | James | Finance
(9 行记录)

UNION ALL 操作符：可以连接两个有重复行的 SELECT 语句

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 子句连接起来：*/
mydb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
mydb-#    ON COMPANY.ID = DEPARTMENT.EMP_ID
mydb-#    UNION ALL
mydb-# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
mydb-#     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
        | James |
        | Paul  |
        | James |
(17 行记录)