Sqlite 简明教程
SQLite - UNION Clause
SQLite UNION 子句/运算符用于合并两个或更多 SELECT 语句的结果，而不返回任何重复行。
SQLite UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.
要使用 UNION，每个 SELECT 都必须选择相同数量的列，相同数量的列表达式，相同的数据类型，并且按相同的顺序排列，但不必相同长度。
To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be of the same length.
Syntax
以下是 UNION 的基本语法。
Following is the basic syntax of UNION.
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]这里给定的条件可以是根据您的要求给出的任何表达式。
Here the given condition could be any given expression based on your requirement.
Example
考虑以下两张表，(a) COMPANY 表如下 −
Consider the following two tables, (a) COMPANY table as follows −
sqlite> select * from COMPANY;
ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0(b) 另一张表 DEPARTMENT 如下：
(b) Another table is DEPARTMENT as follows −
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现在让我们使用 SELECT 语句和 UNION 子句将这两个表联接如下 −
Now let us join these two tables using SELECT statement along with UNION clause as follows −
sqlite> 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;这将产生以下结果。
This will produce the following result.
EMP_ID NAME DEPT
---------- -------------------- ----------
1 Paul IT Billing
2 Allen Engineering
3 Teddy Engineering
4 Mark Finance
5 David Engineering
6 Kim Finance
7 James FinanceThe UNION ALL Clause
UNION ALL 运算符用于合并两个 SELECT 语句的结果，包括重复行。
The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.
适用于 UNION 的相同规则也适用于 UNION ALL 运算符。
The same rules that apply to UNION apply to the UNION ALL operator as well.
Syntax
以下是 UNION ALL 的基本语法。
Following is the basic syntax of UNION ALL.
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]这里给定的条件可以是根据您的要求给出的任何表达式。
Here the given condition could be any given expression based on your requirement.
Example
现在，让我们在我们的 SELECT 语句中连接上述两个表，如下所示 −
Now, let us join the above-mentioned two tables in our SELECT statement as follows −
sqlite> 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;这将产生以下结果。
This will produce the following result.
EMP_ID NAME DEPT
---------- -------------------- ----------
1 Paul IT Billing
2 Allen Engineering
3 Teddy Engineering
4 Mark Finance
5 David Engineering
6 Kim Finance
7 James Finance
1 Paul IT Billing
2 Allen Engineering
3 Teddy Engineering
4 Mark Finance
5 David Engineering
6 Kim Finance
7 James Finance