SQLite’s UNION Clause / operator is used to merge two or more
SELECT
Statement and does not return any duplicate rows.
In order to use UNION The basic syntax is as follows: The condition given here can be any expression as needed. Suppose there are two tables, (1) （2）另一个表是 Now, let’s use the This will produce the following results: Applicable to UNION ALL The basic syntax is as follows: The condition given here can be any expression as needed. Now, let’s use the This will produce the following results:
UNION
, each
SELECT
The number of columns selected must be the same, the same number of column expressions, the same data type, and make sure they are in the same order, but they do not have to have the same length. 1.30.1. Grammar ¶
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
1.30.2. Example ¶
COMPANY
The table is 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
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
SELECT
Statement and
UNION
Clause to join two tables, as followssqlite> 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
---------- -------------------- ----------
1 Paul IT Billing
2 Allen Engineerin
3 Teddy Engineerin
4 Mark Finance
5 David Engineerin
6 Kim Finance
7 James Finance
1.30.3. UNION ALL clause ¶
UNION
ALL
Operator is used to combine two
SELECT
Statement, including duplicate rows.
UNION
The rules also apply to
UNION
ALL
Operator. 1.30.4. Grammar ¶
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
1.30.5. Example ¶
SELECT
Statement and
UNION
ALL
Clause to join two tables, as followssqlite> 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 Engineerin
3 Teddy Engineerin
4 Mark Finance
5 David Engineerin
6 Kim Finance
7 James Finance
1 Paul IT Billing
2 Allen Engineerin
3 Teddy Engineerin
4 Mark Finance
5 David Engineerin
6 Kim Finance
7 James Finance