YouTip

# SQLite Unions Clause The SQLite **UNION** clause/operator is used to combine the results of two or more SELECT statements, returning no duplicate rows. To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and ensure they have the same order, but they do not need to have the same length. ## Syntax The basic syntax of **UNION** is as follows: SELECT column1 [, column2 ] FROM table1 [, table2 ] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] The condition given here can be any expression as needed. ## Example Consider the following two tables, (1) the COMPANY 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 (2) Another table is DEPARTMENT, as follows: ID DEPT EMP_ID ---------- -------------------- ---------- 1 IT Billing 2 Engineering 1 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6 Now, let us use SELECT statements with UNION clause to join the 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 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 ## UNION ALL Clause The UNION ALL operator is used to combine the results of two SELECT statements, including duplicate rows. The same rules that apply to UNION apply to the UNION ALL operator as well. ## Syntax The basic syntax of **UNION ALL** is as follows: SELECT column1 [, column2 ] FROM table1 [, table2 ] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] The condition given here can be any expression as needed. ## Example Now, let us use SELECT statements with UNION ALL clause to join the 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 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