The UNION Clause in SQLite: A Complete Guide With Practical Examples

There’s a specific moment in almost every SQL developer’s journey when they realize they need to combine results from two separate queries into one unified result set. Maybe you’re merging data from two similar tables, or maybe you’re building a report that pulls from customers and suppliers and wants them listed together as one list of “contacts.” That’s exactly what the UNION clause is for, and I want to walk you through it in detail — the syntax, the rules, the differences between UNION and UNION ALL, and plenty of real examples.

What Is UNION?

UNION is a SQL clause that combines the result sets of two or more SELECT statements into a single result set. Instead of running two separate queries and manually merging the results in your application code, UNION lets the database do that work for you directly, in one query.

This is different from a JOIN. A JOIN combines columns from multiple tables side by side, based on some relationship between them. UNION, on the other hand, stacks rows from multiple queries vertically, one result set on top of another, as long as the shape of the data matches up.

Basic Syntax

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

The key rule you need to remember is this: every SELECT statement combined with UNION must return the same number of columns, and those columns must be of compatible types, in the same order. SQLite doesn’t require the column names to match — only the number and general type compatibility of columns. The final result set’s column names come from the very first SELECT statement in the chain.

A Simple Example

Let’s say I have two tables, customers and suppliers, and I want a single unified list of everyone’s name and what kind of contact they are.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE suppliers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

SELECT name, 'customer' AS contact_type FROM customers
UNION
SELECT name, 'supplier' AS contact_type FROM suppliers;

This produces a single result set with two columns — name and contact_type — combining rows from both tables into one clean list.

UNION vs. UNION ALL: The Critical Difference

This is the single most important thing to understand about UNION, and it’s the detail that catches people off guard the most often, especially when it comes to performance.

Plain UNION automatically removes duplicate rows from the combined result set. It behaves like SELECT DISTINCT applied across the entire combined output. UNION ALL, on the other hand, keeps every row from every SELECT statement, including exact duplicates.

SELECT name FROM customers
UNION
SELECT name FROM suppliers;
-- Removes any exact duplicate rows across both queries

SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;
-- Keeps all rows, including duplicates

Here’s why this matters so much: removing duplicates requires SQLite to sort and compare every row in the combined result set, which is genuinely expensive on large data sets. UNION ALL skips that step entirely, simply concatenating the results together, which makes it noticeably faster.

My rule of thumb: use UNION ALL by default unless you specifically need duplicates removed. In my experience, a huge number of UNION queries in the wild don’t actually need deduplication — the two source queries naturally produce non-overlapping data (like the customers/suppliers example above, where a “customer” row and a “supplier” row would never be truly identical because of the contact_type label). In those cases, using plain UNION wastes performance for no real benefit.

Column Matching Rules

Let’s dig a little deeper into how SQLite matches up columns between the combined SELECT statements.

SELECT id, name, email FROM customers
UNION
SELECT id, name, phone FROM suppliers;

This is perfectly valid syntactically — SQLite doesn’t care that the third column is called email in one query and phone in the other. Both queries return three columns, so they line up positionally: the first column of each SELECT becomes the first column of the result, the second becomes the second, and so on. The final output’s column headers come from the first SELECT statement, so in this case, the third column in the combined result would be labeled email, even though half the actual data in that column came from the phone column of suppliers.

This flexibility is convenient, but it also means you need to be careful — it’s entirely possible to accidentally combine semantically unrelated columns (like combining a price column from one table with a quantity column from another) without SQLite raising any error, because as far as SQLite’s type system is concerned, both are just numeric values. Always double check that your columns genuinely correspond to the same real-world concept before combining them with UNION.

Mismatched Column Counts

If your SELECT statements don’t have the same number of columns, SQLite will throw an error:

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

Always double-check column counts, especially in queries that have grown organically over time with columns added or removed from one side but not the other.

Ordering Results From a UNION Query

You cannot put an ORDER BY clause on each individual SELECT statement within a UNION chain (with one narrow exception involving LIMIT, discussed below). Instead, ORDER BY applies to the entire combined result set and must be placed at the very end of the whole statement.

SELECT name, 'customer' AS contact_type FROM customers
UNION
SELECT name, 'supplier' AS contact_type FROM suppliers
ORDER BY name;

Notice ORDER BY comes after the last SELECT in the chain, and it applies to the combined output as a whole, not to either individual query. You can reference either the column name or its position (like ORDER BY 1) in this final ORDER BY.

Using LIMIT With UNION

Similarly, LIMIT generally applies to the final combined result set, placed at the very end:

SELECT name FROM customers
UNION
SELECT name FROM suppliers
ORDER BY name
LIMIT 10;

If you genuinely need to limit an individual SELECT statement before it gets combined with UNION, SQLite allows this only if you wrap that individual query in parentheses:

SELECT name FROM (SELECT name FROM customers ORDER BY name LIMIT 5)
UNION
SELECT name FROM (SELECT name FROM suppliers ORDER BY name LIMIT 5);

This is a less common pattern, but it’s useful when you specifically want, say, the “top 5” from each source before merging them, rather than the “top 10” from the merged whole.

Combining More Than Two Queries

UNION isn’t limited to two SELECT statements — you can chain as many as you need.

SELECT name, 'customer' AS source FROM customers
UNION ALL
SELECT name, 'supplier' AS source FROM suppliers
UNION ALL
SELECT name, 'partner' AS source FROM partners;

SQLite processes these left to right, combining each subsequent SELECT statement’s results with the running combined set.

UNION With WHERE Clauses

Each individual SELECT statement in a UNION chain can have its own independent WHERE clause, applied before the results are combined.

SELECT name, salary FROM employees WHERE department = 'Engineering'
UNION ALL
SELECT name, salary FROM employees WHERE department = 'Sales';

Admittedly, in this particular example, you’d more naturally just use WHERE department IN ('Engineering', 'Sales') in a single query rather than a UNION, but this illustrates the point: filtering happens independently within each SELECT before the merge occurs.

UNION becomes genuinely useful, rather than just an alternative syntax for something simpler, when the two queries pull from different tables or apply fundamentally different logic that can’t be expressed as a single WHERE condition.

Practical Example: Combining Historical and Current Data

A pattern I use fairly often is combining an “active” table with an “archive” table that share the same structure.

SELECT id, name, status, created_at FROM active_orders
UNION ALL
SELECT id, name, status, created_at FROM archived_orders
ORDER BY created_at DESC;

This lets me query across both tables as if they were one, without having to physically merge the data or write duplicate application logic for each source.

Practical Example: Building a Unified Search Across Multiple Tables

Suppose I want to build a simple search feature that looks across products, articles, and categories, returning a unified list of results with a type label so the UI knows what kind of result each row represents.

SELECT id, title AS result_title, 'product' AS result_type FROM products WHERE title LIKE '%laptop%'
UNION ALL
SELECT id, title AS result_title, 'article' AS result_type FROM articles WHERE title LIKE '%laptop%'
UNION ALL
SELECT id, name AS result_title, 'category' AS result_type FROM categories WHERE name LIKE '%laptop%';

This is a genuinely practical, real-world use of UNION — it lets a single query serve a multi-source search feature cleanly.

UNION vs. INTERSECT vs. EXCEPT

SQLite also supports two related set operators worth knowing about alongside UNION:

• INTERSECT returns only rows that appear in both result sets.
• EXCEPT returns rows that appear in the first result set but not in the second.

SELECT email FROM newsletter_subscribers
INTERSECT
SELECT email FROM customers;
-- Finds subscribers who are also customers

SELECT email FROM newsletter_subscribers
EXCEPT
SELECT email FROM customers;
-- Finds subscribers who are NOT customers

These follow the exact same column-matching rules as UNION — same number of columns, compatible types, positional matching. I mention them here because once you understand UNION, INTERSECT and EXCEPT are trivially easy to pick up, and they solve related but distinctly different problems.

Common Mistakes I See With UNION

Using UNION when UNION ALL would suffice. This is far and away the most common performance mistake. If you know your data can’t realistically overlap, or if duplicates genuinely don’t matter for your use case, use UNION ALL and save the deduplication overhead.

Mismatched column types causing unexpected coercion. If one SELECT returns a TEXT column and the corresponding column in another SELECT returns an INTEGER, SQLite’s flexible typing will generally let this through, but the resulting mixed-type column can behave unpredictably in later sorting or comparison operations. Try to keep types consistent across unioned columns.

Forgetting that ORDER BY applies to the whole combined set. Trying to add an ORDER BY to an individual SELECT in the middle of a UNION chain (without wrapping it in parentheses) will cause a syntax error or won’t behave as expected.

Not aliasing columns consistently. Since the final column names come from the first SELECT, make sure that first query has clear, well-chosen aliases — they’ll define your entire result set’s column headers.

Best Practices

1. Default to UNION ALL unless you specifically need duplicate removal — it’s faster and more predictable.
2. Add explicit type/source labels (like 'customer' or 'supplier' as I did above) when merging conceptually different sources, so downstream consumers of the data can distinguish the origin of each row.
3. Keep column types consistent across all SELECT statements in the chain.
4. Put ORDER BY and LIMIT at the very end, applying to the full combined result.
5. Double-check column counts whenever you edit one side of a UNION query — it’s an easy thing to forget when adding or removing a column from just one of the queries.
6. Consider a CTE for very long UNION chains to keep each source query readable and separately testable before you combine everything together.

UNION Inside Common Table Expressions

UNION combines particularly well with common table expressions (CTEs), especially recursive CTEs, which actually require a UNION or UNION ALL internally as part of their structure.

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

This is a genuinely powerful pattern for traversing hierarchical data, like a category tree with unlimited nesting depth, or an organizational chart. The first SELECT establishes the “base case” (top-level categories with no parent), and the second SELECT, combined via UNION ALL, recursively joins back to the CTE itself to pull in each subsequent level. Note that recursive CTEs specifically require UNION ALL in most practical cases (or plain UNION, which additionally prevents infinite loops from duplicate rows, though at a performance cost) — this is one of the few places where the choice between UNION and UNION ALL can affect not just performance, but correctness and even termination of the query.

Type Affinity and UNION

SQLite’s flexible type system means that combining columns of different declared types via UNION generally won’t throw an error, but it’s worth understanding what actually happens to the data. Each unioned column takes on a “type affinity” based on the columns being combined, and values are compared and sorted according to SQLite’s standard type ordering rules (NULL < INTEGER/REAL < TEXT < BLOB) when deduplication or the following ORDER BY needs to compare values across the combined result set.

SELECT quantity FROM warehouse_a
UNION
SELECT quantity_text FROM warehouse_b;

If quantity is stored as INTEGER in one table and quantity_text is stored as TEXT in another (perhaps due to inconsistent schema design across systems), the combined result set will contain a mix of numeric and text values in the same column. This can lead to confusing sort orders or comparison behavior downstream. I strongly recommend explicitly casting columns to a consistent type before unioning them if there’s any chance of a type mismatch:

SELECT CAST(quantity AS INTEGER) AS quantity FROM warehouse_a
UNION
SELECT CAST(quantity_text AS INTEGER) AS quantity FROM warehouse_b;

Performance Notes for Large UNION Queries

When working with genuinely large data sets, a few performance considerations are worth keeping in mind:

• UNION requires a sort-and-deduplicate pass across the entire combined result, which can be memory and CPU intensive for large result sets. UNION ALL skips this step entirely.
• Indexes on the individual source queries still apply. Each SELECT statement in a UNION chain is optimized independently before the results are combined, so make sure each individual query is well-indexed on its own merits.
• Consider materializing very large UNION results into a temporary table if you plan to query the combined result multiple times within the same session, rather than re-running the full UNION query repeatedly.

Frequently Asked Questions

Can I use UNION with different table structures entirely?

Yes, as long as the number of columns and their general type compatibility line up between each SELECT statement. The source tables themselves can be completely unrelated in structure — UNION only cares about the shape of the SELECT output, not the underlying table schemas.

Does UNION preserve row order from the original queries?

Not reliably, unless you add an explicit ORDER BY at the end of the combined query. Without ORDER BY, the order of rows in a UNION result set is not guaranteed and can vary based on SQLite’s internal query execution plan.

Can I use UNION with SELECT * ?

Yes, but I’d generally advise caution — SELECT * in a UNION context ties your query’s correctness to the exact column order of the underlying tables, which can silently break if a column is later added, removed, or reordered in one table but not the other. Explicit column lists are safer for UNION queries specifically.

Is there a limit to how many SELECT statements I can UNION together?

SQLite does have an internal limit on the number of terms in a compound SELECT statement (controlled by SQLITE_LIMIT_COMPOUND_SELECT, defaulting to 500), but in practice, this is far more than what any reasonable query would need. If you find yourself needing hundreds of unioned queries, it’s usually a sign the underlying data model could be simplified.

Wrapping Up

UNION is a wonderfully simple tool once you understand its core mechanics: it stacks the results of multiple SELECT statements vertically, requires matching column counts and compatible types, and comes in two flavors — UNION, which removes duplicates, and UNION ALL, which doesn’t. The performance difference between those two flavors alone is worth memorizing, since defaulting to plain UNION out of habit is one of the most common unnecessary slowdowns I see in real SQLite codebases.

Next time you find yourself wanting to merge results from two different tables, or building a unified view across similar but separate data sources, reach for UNION — and remember to ask yourself honestly whether you actually need the deduplication UNION provides, or whether UNION ALL would do the job faster.