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