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 表(下载 COMPANY SQL 文件 ),数据内容如下:
yssmxdb# 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 表(下载 DEPARTMENT SQL 文件 ),数据内容如下:
yssmxdb=# 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)