SQLite Union Operator

SQLite is a server-less database engine and it is written in c programming language. It is developed by D. Richard Hipp in the year 2000. The main moto for developing SQLite is to escape from using 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 UNION Operator in SQLite also their examples, and so on. After reading this you will have decent knowledge about the UNION Operator and you can easily perform queries.

SQLite UNION Operator

SQLite UNION operator is used to combine the result set of different SELECT statements and to fetch the output as a single result set. It is used to remove the duplicate rows that are fetched. However, there needs to be a similar number of columns or expressions that need to be specified in each SELECT statement and even the columns must be of the same data type too.

• The 2 select statements must have the same number of columns.
• The main function of the UNION operator is not to fetch the duplicate columns and that affects the result set. So, using the UNION DISTINCT operator does not affect the result set.
• The column names of the first table are used as the column names for the result set.

Syntax:

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

Explanation: Here in the syntax, we can observe that we are using two select statements followed by the table name from which we are going to select the data and WHERE condition, If and only if the condition is met then the query is executed otherwise the query stops executing and we may not be able to fetch the data that we want to retrieve. If it meets the condition then it is going to fetch the other tables data and even it needs to satisfy the second WHERE condition too.

Visual Representation of UNION Operator

Union Operator Structure

Here In the image we can clearly saw that we have two columns and they are named as SELECT A, SELECT B and they are having the same data type combined together with the UNION operator to form the single column. In the UNION Column their is no duplicate value present which signifies that UNION Operator remove the duplicates values. This is how the UNION operator works.

Example of SQLite UNION Operator

To understand the UNION Operator we need a two table on which we will perform some operations and queries. Here we have created to table called department and employee. The department table consist of dept_id, dept_name. The employee table consist of dept_id, emp_id, first_name, and last_name as Columns. iF you don’t know How to Create Tables in SQLite then refer this.

After inserting some data into the department table.

department table

After inserting some data into the employee table.

Employee table

Example 1: Simple UNION Operator

Let’s Combine department and employee table info, but include only departments with ID greater than 4.

Query:

SELECT dept_id, dept_name
FROM  department 
WHERE  dept_id>4
UNION 
SELECT emp_id, first_name
FROM employee;

Output:

Union Operator

Explanation: Here we have fetch the department id and department name from department table with the condition that departmnet id must be greater than 4 and now we use UNION keyword to combine the two queries and followed by the second select query and we are going to fetch the employee id, first name from the employees table. In the output we got 8 rows fetched in the combination of dept_id, emp_id, dept_name and first_name. The columns with same data type are combined.

Example 2: UNION Operator with ORDER By Clause

Now we are going to use UNION 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.

Syntax:

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

Query:

Let’s Combine the department & employee data, include only department with ID > 3, then sort employees by last_name.

SELECT dept_id, dept_name
FROM department
WHERE dept_id > 3
UNION
SELECT emp_id, last_name
FROM employee
ORDER BY last_name;

Output:

Union with Order by

Explanation: Here we have fetch the department id and name from the department table where department id is greater than 3 and we have unite employee table and fetched the employee id and lastname where result set is ordered by lastname. In the ouput we can observe that the four columns are united according to their data type and the order is arranged by using last_name in ascending order.

Conclusion

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