Here we will learn how to use SQLite UNION All operator with example and how to use SQLite UNION ALL operator to combine and return multiple select statement result sets including duplicate values with example.
In SQLite UNION ALL operator is used to combine the result sets of 2 or more SELECT statements and it will return all the rows including duplicates.
The SELECT statements which we used with UNION ALL operator must have the same number of fields in the result sets with similar data types.
Following is the syntax of SQLite UNION ALL operator to combine multiple select statement result sets and return all records including duplicates.
SELECT expression1, expression2,... expression_n
FROM tables
[WHERE conditions]
UNION ALL
SELECT expression1, expression2,... expression_n
FROM tables
[WHERE conditions];
If you observe above SQLite UNION ALL syntax we are combing multiple select statements using UNION ALL operator and we used some properties those are
expression1, expression2, ... expression_n - The columns or calculations that you wish to retrieve.
tables - The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions - Optional. The conditions that must be met for the records to be selected.
While using SQLite UNION ALL operator it’s important to note that all the SELECT statements must contain the same number of columns or expressions with the same data type and that columns order also must be the same.
The SQLite UNION ALL operator will use column names from the first SELECT statement as the column names for the result set.
We will see how to use SQLite UNION ALL operator with example. For this example, we need to create two tables dept_master and emp_master and need to insert some data for that use following queries.
To create and insert some data in the dept_master table execute the following query.
CREATE TABLE dept_master
(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
dept_name TEXT);
INSERT INTO dept_master(dept_name)
VALUES('Admin'),
('Sales'),
('Quality Control');
('Marketing');
Once we create and insert data execute following query to see the data of dept_master table.
sqlite> SELECT * FROM dep_master;
dept_id dept_name
---------- ----------
1 Admin
2 Sales
3 Quality Control
4 Marketing
Same way execute the following queries to create and insert some data in the emp_master table.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (1,'Honey','Patel',10100,1),
(2,'Shweta','Jariwala', 19300,2),
(3,'Vinay','Jariwala', 35100,3),
(4,'Jagruti','Viras', 9500,2),
(5,'Shweta','Rana',12000,3),
(6,'sonal','Menpara', 13000,1),
(7,'Yamini','Patel', 10000,2),
(8,'Khyati','Shah', 500000,3);
Now run the following query to check records of emp_master table.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
Now write SQLite query like as shown following to use UNION ALL operator to combine multiple select statement result sets and return all the records.
SELECT dept_id
FROM dept_master
UNION ALL
SELECT dept_id
FROM emp_master
Now we will run and see the output of SQLite UNION ALL operator example that will be like as shown below.
dept_id
----------
4
1
2
2
1
1
2
3
3
2
3
3
If you observe the above example SQLite UNION ALL operator returned all the records from both the select statements including duplicate records.
Generally, the SQLite UNION ALL operator will combine multiple select statement result sets and return all the records including duplicates. So if we want to use Order By clause with UNION ALL operator we need to define Order By at the end of last Select statement.
Following is the syntax of using SQLite Union ALL with Order By in Select statement.
SELECT expression1, expression2,... expression_n
FROM tables1
[WHERE conditions]
UNION ALL
SELECT expression1, expression2,... expression_n
FROM tables1
[WHERE conditions] ORDER BY expression1
Now we will see the example of using SQLite UNION ALL operator with Order By in select statement.
SELECT dept_id
FROM dept_master
UNION ALL
SELECT dept_id
FROM emp_master order by dept_id;
If you observe above SQLite UNION ALL operator query we used Order By clause at the end of a select statement. Now we will run and see the output that will be like as shown below.
dept_id
----------
1
1
1
2
2
2
2
3
3
3
3
4
If you observe the above result it included all the duplicate dept_id of both the tables.
This is how we can use SQLite UNION ALL operator to combine multiple select statement result sets and to return ALL the records including duplicate ones.