HTML tutorial
CSS3 tutorial
Bootstrap tutorial
JavaScript tutorial
JQuery tutorial
AngularJS tutorial
React tutorial
NodeJS tutorial
PHP tutorial
Python tutorial
Python3 tutorial
Django tutorial
Linux tutorial
Docker tutorial
Ruby tutorial
Java tutorial
C tutorial
C ++ tutorial
Perl tutorial
JSP tutorial
Lua tutorial
Scala tutorial
Go tutorial
ASP.NET tutorial
C # tutorial
TheUNION clause SQLite / operator is used to combine two or more of the SELECT statement, it does not return any duplicate rows.
To use the UNION, the number of columns in each SELECT selected must be the same, the same number of column expressions, the same type of data, and to ensure that they have the same order, but they do not necessarily have the same length.
UNION basic syntax is as follows:
SELECT column1 [, column2] FROM table1 [, table2] [WHERE condition] UNION SELECT column1 [, column2] FROM table1 [, table2] [WHERE condition]
Here given the conditions as necessary can be any expression.
Suppose there are two tables below, (1) COMPANY tables are 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
(2) is another table DEPARTMENT, as follows:
ID DEPT EMP_ID ---------- -------------------- ---------- 1 Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6
Now, let's use the SELECT statement and the UNION clause to join two tables, 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 produces the following results:
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
UNION ALL operator is used to combine the results of two SELECT statements, including duplicates.
UNION applies the same rules apply to UNION ALL operator.
The basic syntax forUNION ALL is as follows:
SELECT column1 [, column2] FROM table1 [, table2] [WHERE condition] UNION ALL SELECT column1 [, column2] FROM table1 [, table2] [WHERE condition]
Here given the conditions as necessary can be any expression.
Now, let's use the SELECT statement and UNION ALL clause to join two tables, 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 produces the following results:
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