SQLite UNION and UNION ALL: Combine Query Results (Tested Examples)

Ninad Pathak

October 7, 2026

Joins put columns side by side, while UNION stacks the rows of two queries into one result, like customers and suppliers in one contact list. In this article I’ll walk you through how it works in SQLite and when UNION ALL is the better pick, then use it to merge two database files.

TLDR

SELECT … UNION SELECT … returns the rows of both queries, with duplicate rows removed.
UNION ALL keeps every row, duplicates included.
Each SELECT must return the same number of columns. The column names come from the first one.

ORDER BY and LIMIT go once, at the very end, and apply to the combined result.
To merge two database files, ATTACH the second one, then UNION the two tables.

What is UNION in SQLite?

UNION is a compound operator that joins the results of two SELECT statements into one result set. The SQLite SELECT docs describe four of these operators:

Operator	Returns
`UNION ALL`	Every row from the left query, then every row from the right
`UNION`	The same rows, with duplicate rows removed
`INTERSECT`	Rows that appear in both results
`EXCEPT`	Rows from the left result that aren’t in the right

SQLite UNION syntax

SELECT column1, column2 FROM table1 [WHERE …]
UNION [ALL]
SELECT column1, column2 FROM table2 [WHERE …]
[ORDER BY …] [LIMIT …];

The rules, from the same docs:

Same column count. Every SELECT must return the same number of columns.
Matched by position. Columns line up by their place in the list, not by name.
ORDER BY at the end. Only the last SELECT may have ORDER BY or LIMIT. They apply to the combined result.

How to combine two tables with UNION

To show you how this works, I will use a customers table and a suppliers table. Ada is in both:

sqlite> CREATE TABLE customers (name TEXT, email TEXT, city TEXT);
sqlite> CREATE TABLE suppliers (company TEXT, email TEXT, city TEXT);
sqlite> INSERT INTO customers VALUES ('Ada Lovelace', 'ada@example.com', 'London'), ('Linus T', 'linus@example.com', 'Helsinki'), ('Grace H', 'grace@example.com', 'New York');
sqlite> INSERT INTO suppliers VALUES ('Acme Ltd', 'sales@acme.example', 'London'), ('Nordic Parts', 'info@nordic.example', 'Helsinki'), ('Ada Lovelace', 'ada@example.com', 'London');

Step 1: Pick matching columns from each table

The two tables name their columns differently (name and company), which doesn’t matter. What matters is that both queries return a name and then an email.

Step 2: Join the two queries with UNION

sqlite> SELECT name, email FROM customers UNION SELECT company, email FROM suppliers;
name          email
------------  -------------------
Acme Ltd      sales@acme.example
Ada Lovelace  ada@example.com
Grace H       grace@example.com
Linus T       linus@example.com
Nordic Parts  info@nordic.example

Ada appears once, because UNION removes rows that are identical in every column. The result is only in this order by chance. Without ORDER BY, SQLite doesn’t promise any order.

Examples

These examples use the same two tables.

Keep duplicates with UNION ALL

sqlite> SELECT name, email FROM customers UNION ALL SELECT company, email FROM suppliers;
name          email
------------  -------------------
Ada Lovelace  ada@example.com
Linus T       linus@example.com
Grace H       grace@example.com
Acme Ltd      sales@acme.example
Nordic Parts  info@nordic.example
Ada Lovelace  ada@example.com

Ada appears twice now. Use UNION ALL when you want every row, or when you know there can’t be duplicates. It skips the de-duplication step.

Removing duplicates compares whole rows. With only the city selected, three rows remain out of six:

sqlite> SELECT city FROM customers UNION SELECT city FROM suppliers;
city
--------
Helsinki
London
New York
sqlite> SELECT city FROM customers UNION ALL SELECT city FROM suppliers;
city
--------
London
Helsinki
New York
London
Helsinki
London

Sort the combined result

Put one ORDER BY after the last query. It sorts the whole result:

sqlite> SELECT name, city FROM customers UNION SELECT company, city FROM suppliers ORDER BY city, name;
name          city
------------  --------
Linus T       Helsinki
Nordic Parts  Helsinki
Acme Ltd      London
Ada Lovelace  London
Grace H       New York

Label where each row came from

A constant column tells you which query a row came from. Because it makes the two Ada rows different, both stay:

sqlite> SELECT 'customer' AS kind, name, email FROM customers UNION ALL SELECT 'supplier', company, email FROM suppliers ORDER BY kind, name;
kind      name          email
--------  ------------  -------------------
customer  Ada Lovelace  ada@example.com
customer  Grace H       grace@example.com
customer  Linus T       linus@example.com
supplier  Acme Ltd      sales@acme.example
supplier  Ada Lovelace  ada@example.com
supplier  Nordic Parts  info@nordic.example

Filter each query with its own WHERE

Each SELECT can have its own WHERE clause:

sqlite> SELECT name, city FROM customers WHERE city = 'London' UNION SELECT company, city FROM suppliers WHERE city = 'London';
name          city
------------  ------
Acme Ltd      London
Ada Lovelace  London

Count the combined rows

Wrap the compound query in a subquery to count or aggregate it:

sqlite> SELECT count(*) AS contacts FROM (SELECT email FROM customers UNION SELECT email FROM suppliers);
contacts
--------
5

Find rows in both tables, or in only one

INTERSECT and EXCEPT use the same rules as UNION:

sqlite> SELECT email FROM customers INTERSECT SELECT email FROM suppliers;
email
---------------
ada@example.com
sqlite> SELECT email FROM customers EXCEPT SELECT email FROM suppliers;
email
-----------------
grace@example.com
linus@example.com

Merge two SQLite databases in Python

To combine tables from two database files, attach both files and UNION the tables. This script merges the contacts in shop_a.db and shop_b.db into a new merged.db, where Ada appears in both:

$ sqlite3 shop_a.db "SELECT * FROM contacts;"
ada@example.com|Ada
linus@example.com|Linus
$ sqlite3 shop_b.db "SELECT * FROM contacts;"
ada@example.com|Ada
grace@example.com|Grace

import sqlite3

# Combine the contacts in shop_a.db and shop_b.db into merged.db, without duplicates.
con = sqlite3.connect("merged.db")
con.execute("ATTACH DATABASE 'shop_a.db' AS a")
con.execute("ATTACH DATABASE 'shop_b.db' AS b")
con.execute("CREATE TABLE IF NOT EXISTS contacts (email TEXT PRIMARY KEY, name TEXT)")
with con:  # one transaction
    con.execute("""
        INSERT OR IGNORE INTO contacts (email, name)
        SELECT email, name FROM a.contacts
        UNION
        SELECT email, name FROM b.contacts
    """)
for row in con.execute("SELECT email, name FROM contacts ORDER BY email"):
    print(row)
con.close()

$ python3 merge_dbs.py
('ada@example.com', 'Ada')
('grace@example.com', 'Grace')
('linus@example.com', 'Linus')

UNION drops the duplicate Ada row, and INSERT OR IGNORE skips any email that’s already in merged.db if you run the script again. See SQLite ATTACH DATABASE and INSERT INTO SELECT for more on both.

Edge cases

Each of these comes from the test run.

The column counts don’t match

sqlite> SELECT name, email FROM customers UNION SELECT company FROM suppliers;
Error: in prepare, SELECTs to the left and right of UNION do not have the same number of result columns

Add a NULL or a constant to the shorter query so both return the same number of columns.

ORDER BY inside one of the queries

sqlite> SELECT name FROM customers ORDER BY name UNION SELECT company FROM suppliers;
Error: in prepare, ORDER BY clause should come after UNION not before

To sort or limit one part on its own, wrap it in a subquery:

sqlite> SELECT * FROM (SELECT name FROM customers ORDER BY name LIMIT 1) UNION ALL SELECT * FROM (SELECT company FROM suppliers ORDER BY company LIMIT 1);
name
------------
Ada Lovelace
Acme Ltd

Column names come from the first query

The second query’s alias is ignored:

sqlite> SELECT name AS contact FROM customers UNION SELECT company AS supplier_name FROM suppliers LIMIT 2;
contact
------------
Acme Ltd
Ada Lovelace

Values of different types aren’t duplicates

SQLite compares values exactly as stored, with no type conversion. So the integer 1 and the text ‘1’ are both kept. A pair of NULLs, on the other hand, counts as a duplicate, so one NULL row comes back:

sqlite> SELECT 1 AS v, typeof(1) AS type UNION SELECT '1', typeof('1');
v  type
-  -------
1  integer
1  text
sqlite> SELECT NULL AS v UNION SELECT NULL;
v
----
NULL

Conclusion

Use UNION to stack the rows of two queries into one list without duplicates. Use UNION ALL when duplicates are fine or impossible. Keep the column count and order the same in every query, and put a single ORDER BY at the end.