Here we will learn sqlite union operator with example and how to use sqlite union operator with an order by clause to combine the result of multiple select statements and show only unique records with example.
In SQLite UNION operator is used to combine the result sets of 2 or more SELECT statements and it removes duplicate rows between the various SELECT statements.
The SELECT statements which we used with the UNION operator must have the same number of fields in the result sets with similar data types.
Following is the syntax of SQLite UNION operator to combine multiple select statement result sets and return unique records.
SELECT expression1, expression2,... expression_n
FROM tables
[WHERE conditions]
UNION
SELECT expression1, expression2,... expression_n
FROM tables
[WHERE conditions];
If you observe above SQLite UNION syntax we are combing multiple select statements using UNION operator and we used some properties those are
While using SQLite UNION operator its 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 operator by default removes all duplicate rows from the result set and the column names from the first SELECT statement in the UNION operator are used as the column names for the result set.
We will see how to use SQLite UNION 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 operator to combine multiple select statement result sets and return unique elements.
SELECT dept_id
FROM dept_master
UNION
SELECT dept_id
FROM emp_master
Now we will run and see the output of SQLite UNION operator example that will be like as shown below.
dept_id
----------
2
4
1
2
If you observe the above example SQLite UNION operator removed duplicate rows from the result set.
Generally, the SQLite UNION operator will combine multiple select statement result sets and return unique records. So if we want to use Order By clause with UNION operator we need to define Order By at the end of last Select statement.
Following is the syntax of using SQLite Union with Order By in Select statement.
SELECT expression1, expression2,... expression_n
FROM tables1
[WHERE conditions]
UNION
SELECT expression1, expression2,... expression_n
FROM tables1
[WHERE conditions] ORDER BY expression1
Now we will see the example of using SQLite UNION operator with Order By in the select statement.
SELECT dept_id
FROM dept_master
UNION
SELECT dept_id
FROM emp_master order by dept_id;
If you observe the above SQLite UNION 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
2
3
4
This is how we can use SQLite UNION operator to combine multiple select statement result sets and to return only UNIQUE elements.