赞
踩
目录
UNION 操作符:合并两个或多个 SELECT 语句的结果
UNION ALL 操作符:可以连接两个有重复行的 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 操作符选取不同的值。
如果允许重复的值,请使用 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 行记录)
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。