SQLite Union All

by Online Tutorials Library July 14, 2022

SQLite Union All Operator

The SQLite UNION ALL operator is used to combine the result of two or more SELECT statement without ignoring the duplicate rows.

In SQLite UNION ALL, the resultant table also includes the duplicate values. Otherwise, the same rule applies as Union.

Syntax:

  SELECT expression1, expression2, … expression_n  FROM tables  [WHERE conditions]  UNION ALL  SELECT expression1, expression2, … expression_n  FROM tables  [WHERE conditions];  

Example:

We have two tables “STUDENT” and “DEPARTMENT”.

Sqlite Union all operator 1

The “STUDENT” table is having the following data:

Sqlite Union all operator 2

The “DEPARTMENT” table is having the following data:

Sqlite Union all 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 ALL.

Output:

Sqlite Union all operator 4

Example2: UNION ALL 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 ALL Clause:

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

Output:

Sqlite Union all operator 5

Next TopicSQLite Joins

SQLite Union All

SQLite Union All Operator

Learn SQLite Tutorial

Difference between function and method in Swift

You may also like