SQLite Union All Operator

SQLite is a server-less database engine written in C programming language. It is developed by D. Richard Hipp in the year 2000. The main moto for developing SQLite is escaping complex database engines like MYSQL etc. It has become one of the most popular database engines as we use it in Television, Mobile Phones, Web browsers, and many more. It is written simply so that it can be embedded into other applications.

In this article, we will learn about the SQLite Union All operator, how it works, and its functionality with various examples.

SQLite UNION ALL Operator

SQLite UNION ALL operator combines multiple select statements and fetches the entire data. Union All fetches the duplicate rows too. The select statement must have the same number of columns with the same data type and the first select statement column names are used as the result set column names. It helps to unite the multiple select statements to retrieve the specified output. In simple words, it is used to fetch data from multiple tables.

• Here expression1, expression2,…..expression_n are nothing but the column names that we want to fetch.
• tables from which we are going to fetch the data.
• where the condition is optional.

SQLite Union All operator

This is how the union all operators is going to work as shown above. In the above example we can observe that two tables are combined together to retrieve the common rows with the duplicate columns.

Syntax:

SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions
UNION ALL
SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions;

Syntax Explanation: Here we are using the SELECT statement to fetch the data from the specified table and the condition of WHERE to be met inorder to move forward and followed by UNION ALL and the second select statement and table name from which we are going to fetch the data followed by the where condition(optional).

Example of SQLite Union All Operator

In this article we are going to use the department and employee table in order to understand the UNION ALL operator and below is the department table with two columns and they are dept_id and dept_name. If we don’t know How to Create Table in SQLite then refer this.

department table

The below is the employee table and table has four columns dept_id, emp_id, last_name and first_name.

Employee table

Example 1: Simple UNION All Operator

Query:

SELECT dept_id, dept_name
FROM department
WHERE dept_id <3
SELECT emp_id, last_name
FROM employee
WHERE emp_id<103;

Output:

Union All table

Explanation: Here we are going to fetch the department Id and name from the department table and where department Id is less than 3 and Union All is performed. The second select statement where we are going to fetch the employee Id and name from the employee table where employee Id is less than 103.
Here we can see that seven rows are fetched with the duplicate rows.

Example 2: UNION ALL Operator with Order By Clause

Now we are going to use UNION All operator with the ORDER BY clause. Order by clause is used to arrange the result set according to the order that we specify in our query. As we know union all is used to unite the multiple select statements and after fetching them the result set is arranged according to the specified query.

Syntax:

SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions
UNION ALL
SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions
ORDER BY;

Syntax Explanation: Here we are using the select statement to fetch the data from the specified table and the Where condition to be met inorder to move forward and followed by UNION ALL operator and the second select statement, followed by the table name from which we are going to fetch the data followed by the where condition and order by the specified column.

Query:

SELECT dept_id, dept_name
FROM department
WHERE dept_id <3
UNION ALL
SELECT emp_id, last_name
FROM employee
WHERE emp_id<103
ORDER BY dept_id desc;

Ouput:

union all order by

Explanation: Here we are fetching the department id, department name, employee id and last name from department and employee table where department id is less than 3 and employee id is less than 103 in the descending order. Here seven rows are fetched with the two columns and they are department id and department name with the duplicate rows.

Example 3: UNION ALL Operator with Inner Join

Inner join is nothing but the simple join. We are going to use inner join with the union all to fetch the specified records from the tables. It only the returns the common records from both the tables.

Union ALL using Inner JOIN

Syntax:

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column
UNION ALL
SELECT columns
FROM table 2;

Syntax Explanation: Here we are going to combine two tables using the common column on dept_id and using INNER JOIN and applying Union All on the two SELECT statements.

Query:

SELECT d.dept_id
FROM department d
INNER JOIN employee e
ON d.dept_id = e.dept_id
UNION ALL
SELECT e.emp_id
FROM employee as e;

Output:

Unionall Inner join

Explanation: In the ouput we can see that eleven rows are retrieved that is dept_id and emp_id as those are the common rows in the two tables thay are joined together.

Example 4: UNION ALL Operator with Outer Join

The SQLite Union All does not support the Outer join. But we can use two left outer joins to form a Union All Outer Join. Below is the syntax that shows how we use left outer join to perform outer join functionality.

Syntax:

SELECT columns
FROM table1 
LEFT JOIN table2
ON table1.column = table2.column
UNION ALL
SELECT columns
FROM table 2
LEFT JOIN table1
ON table1.column = table2.column;

Syntax Explanation: Two select statements followed by the table names and the columns names on which we are going to join them, by swapping the tables twice. We are joining the two tables using the left join inorder to perform the outer join.

Query:

SELECT d.dept_id
FROM department d
LEFT JOIN employee e
ON d.dept_id = e.dept_id
UNION ALL
SELECT e.dept_id
FROM employee as e
LEFT JOIN department d
ON d.dept_id = e.dept_id;

Output:

unionall outer join

Explanation: We are going to unite the department id column from two tables and in order to do that first we need to fetch the data by using the select statement and going to join the tables by using left outer joins. This is how the two left joins are joined together to work as a outer join and the department id column is retrieved with the twelve rows.

Conclusion

In this article we have learnt about the SQLite UNION ALL, UNION WITH ORDER BY operator with examples. UNION ALL is used to combine the result set of multiple select statement and allows duplicate rows and ORDER BY is used to arrange the rows in the specified order. By the end of the article you will get knowledge on the UNION ALL operator that is the functionality, when to use and where to use.