SQLite UNION condition/administrator is utilized to join the consequences of at least two SELECT explanations without restoring any copy columns.
To utilize UNION, each SELECT should have similar number of sections chosen, similar number of segment articulations, a similar information type, and have them in a similar request, however they don't need to be of a similar length.
Syntax
Following is the fundamental language structure 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 articulation dependent on your necessity.
Example
Think about the accompanying 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) 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
Presently let us join these two tables utilizing SELECT explanation alongside UNION statement 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 create the accompanying outcome.
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
The UNION ALL Clause
The UNION ALL administrator is utilized to consolidate the aftereffects of two SELECT articulations including copy lines.
The very standards that apply to UNION apply to the UNION ALL administrator too.
Syntax
Following is the essential grammar 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 articulation dependent on your necessity.
Example
Presently, let us join the previously mentioned two tables in our SELECT assertion 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 create the accompanying outcome.
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