SQLite Union
Summary: in this tutorial, you will learn how to use SQLite UNION
operator to combine result sets of two or more queries into a single result set.
Introduction to SQLite UNION
operator
Sometimes, you need to combine data from multiple tables into a complete result set. It may be for tables with similar data within the same database or maybe you need to combine similar data from multiple databases.
To combine rows from two or more queries into a single result set, you use SQLite UNION
operator. The following illustrates the basic syntax of the UNION
operator:
query_1
UNION [ALL]
query_2
UNION [ALL]
query_3
...;
Both UNION
and UNION ALL
operators combine rows from result sets into a single result set. The UNION
operator removes eliminate duplicate rows, whereas the UNION ALL
operator does not.
Because the UNION ALL
operator does not remove duplicate rows, it runs faster than the UNION
operator.
The following are rules to union data:
- The number of columns in all queries must be the same.
- The corresponding columns must have compatible data types.
- The column names of the first query determine the column names of the combined result set.
- The
GROUP BY
andHAVING
clauses are applied to each individual query, not the final result set. - The
ORDER BY
clause is applied to the combined result set, not within the individual result set.
Note that the difference between UNION
and JOIN
e.g., INNER JOIN
or LEFT JOIN
is that the JOIN
clause combines columns from multiple related tables, while UNION
combines rows from multiple similar tables.
Suppose we have two tables t1 and t2 with the following structures:
CREATE TABLE t1(
v1 INT
);
INSERT INTO t1(v1)VALUES(1),(2),(3);
CREATE TABLE t2(
v2 INT
);
INSERT INTO t2(v2)
VALUES(2),(3),(4);
The following statement combines the result sets of the t1 and t2 table using the UNION
operator:
SELECT v1
FROM t1
UNION
SELECT v2
FROM t2;
Here is the output:
![SQLite UNION example](https://i0.wp.com/www.sqlitetutorial.net/wp-content/uploads/2018/11/SQLite-UNION-example.png?resize=56%2C84&ssl=1)
The following picture illustrates the UNION
operation of t1 and t2 tables:
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/08/SQLite-UNION.png?resize=769%2C214)
The following statement combines the result sets of t1 and t2 table using the UNION ALL
operator:
SELECT v1
FROM t1
UNION ALL
SELECT v2
FROM t2;
The following picture shows the output:
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/08/SQLite-UNION-ALL-example.png?resize=55%2C122)
The following picture illustrates the UNION ALL
operation of the result sets of t1 and t2 tables:
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/08/SQLite-UNION-ALL.png?resize=769%2C214)
SQLite UNION
examples
Let’s take some examples of using the UNION
operator.
1) SQLite UNION
example
This statement uses the UNION
operator to combine names of employees and customers into a single list:
SELECT FirstName, LastName, 'Employee' AS Type
FROM employees
UNION
SELECT FirstName, LastName, 'Customer'
FROM customers;
Here is the output:
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/08/sqlite-union-example.png?resize=241%2C315)
2) SQLite UNION
with ORDER BY
example
This example uses the UNION
operator to combine the names of the employees and customers into a single list. In addition, it uses the ORDER BY
clause to sort the name list by first name and last name.
SELECT FirstName, LastName, 'Employee' AS Type
FROM employees
UNION
SELECT FirstName, LastName, 'Customer'
FROM customers
ORDER BY FirstName, LastName;
Here is the output:
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/08/sqlite-union-with-order-by-example.png?resize=243%2C328)
In this tutorial, you have learned how to use SQLite UNION
operator to combine rows from result sets into a single result set. You also learned the differences between UNION
and UNION ALL
operators.