SQLite Union Operator

SQLite UNION Operator is used to combine the result set of two or more tables using SELECT statement. The UNION operator shows only the unique rows and removes duplicate rows.

While using UNION operator, each SELECT statement must have the same number of fields in the result set.

Syntax:

snippet
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Example:

We have two tables "STUDENT" and "DEPARTMENT".

Sqlite Union operator 1

The "STUDENT" table is having the following data:

Sqlite Union operator 2

The "DEPARTMENT" table is having the following data:

Sqlite Union operator 3

Example1: Return Single Field

This simple example returns only one field from multiple SELECT statements where the both fields have same data type.

Let's take the above two tables "STUDENT" and "DEPARTMENT" and select id from both table to make UNION.

snippet
SELECT ID FROM STUDENT
UNION
SELECT ID FROM DEPARTMENT;

Output:

Sqlite Union operator 4

Example2: Union with Inner and Outer Join

Let's take the above two tables "STUDENT" and "DEPARTMENT" and make an inner join and outer join according to the below conditions along with UNION Clause:

snippet
SELECT EMP_ID, NAME, DEPT FROM STUDENT JOIN DEPARTMENT
ON STUDENT.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM STUDENT LEFT OUTER JOIN DEPARTMENT
ON STUDENT.ID = DEPARTMENT.EMP_ID;

Output:

Sqlite Union operator 5
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents