Have you ever needed to combine data from multiple tables in your SQLite database? The UNION operator is your new best friend.
UNION allows you to merge rows from two or more SELECT queries into one result set. This makes it easy to consolidate data from across tables for reporting and analysis.
In this comprehensive guide, you’ll learn:
- What SQLite UNION is, and when to use it
- UNION syntax and rules
- UNION vs UNION ALL – understanding the difference
- Common errors and fixes when using UNION
- Real-world examples for leveraging UNION effectively
So let’s get started!
Also read: SQLite Triggers: Automating Database Actions
Why Use SQLite UNION?
Here are some common cases where UNION comes in handy:
Consolidating reports – Compile data from various sources/periods into a single view for simpler reporting. For example, combine regional sales data into one global sales report.
Enriching data sets – Append supplemental data from another table to enrich your analyses. Merge employee and transaction tables to attribute sales to staff.
Simplifying queries – Avoid complex joins by unioning associated data as a single virtual table. Merge current and past job history to analyze career progression.
The benefit of UNION is gaining a complete picture by blending datasets as needed, without altering existing tables.
SQLite UNION Syntax
The basic syntax for UNION is:
SELECT columns FROM table1
UNION
SELECT columns FROM table2
Key Rules:
- Both SELECT statements must have an equal number of columns selected
- Corresponding columns must be of the same or compatible data types
- Columns are named after the first SELECT statement
- Sorting via ORDER BY is applied to the final result set
Now let’s see some examples.
Also read: SQLite BLOB Data Type: Storing Binary Data in SQLite
SQLite UNION Example
Consider a media site with separate logs for video and audio streaming:
video_logs
| date | title | views |
|---|---|---|
| 2022-05-01 | Cat Tickles | 15 |
| 2022-05-02 | Yoga Flow | 10 |
audio_logs
| date | title | listens |
|---|---|---|
| 2022-05-01 | Sleepy Jazz | 20 |
| 2022-05-02 | Calm Rain | 8 |
To generate a combined streaming report, we can UNION both tables:
SELECT date, title, views AS engagement
FROM video_logs
UNION
SELECT date, title, listens
FROM audio_logs
ORDER BY date;
Output:
| date | title | engagement |
|---|---|---|
| 2022-05-01 | Cat Tickles | 15 |
| 2022-05-01 | Sleepy Jazz | 20 |
| 2022-05-02 | Yoga Flow | 10 |
| 2022-05-02 | Calm Rain | 8 |
By aliasing the listens column, we effectively merged data into one uniform result set for analysis!
UNION vs UNION ALL
UNION automatically filters out duplicate rows. But using UNION ALL will retain all values – even duplicates:
SELECT columns FROM table1
UNION ALL
SELECT columns FROM table2
When to use UNION ALL?
- You need to allow duplicate values
- Performance boost since duplicate removal is skipped
For our streams report, maybe we do want to see all raw data including duplicates:
SELECT date, title, views
FROM video_logs
UNION ALL
SELECT date, title, listens
FROM audio_logs;
Output:
| date | title | engagement |
|---|---|---|
| 2022-05-01 | Cat Tickles | 15 |
| 2022-05-01 | Sleepy Jazz | 20 |
| 2022-05-02 | Yoga Flow | 10 |
| 2022-05-02 | Calm Rain | 8 |
| 2022-05-02 | Calm Rain | 8 |
Note the duplicate record returned for “Calm Rain” which would have been filtered by regular UNION.
ORDER BY Clause
To sort the final UNION result set, add an ORDER BY at the end:
SELECT columns FROM table1
UNION
SELECT columns FROM table2
ORDER BY column1 DESC, column2 ASC;
Let’s order our streams report by most popular content first:
SELECT date, title, views
FROM video_logs
UNION
SELECT date, title, listens
FROM audio_logs
ORDER BY engagement DESC;
Output:
| date | title | engagement |
|---|---|---|
| 2022-05-01 | Sleepy Jazz | 20 |
| 2022-05-01 | Cat Tickles | 15 |
| 2022-05-02 | Yoga Flow | 10 |
| 2022-05-02 | Calm Rain | 8 |
| 2022-05-02 | Calm Rain | 8 |
Common SQLite UNION Issues
When using UNION, some common errors can occur:
1. Column Mismatch
Error: SELECTs to the left and right have a different number of columns
Solution: Ensure same number of columns picked in all SELECT statements.
2. Data Type Mismatch
Error: Datatype mismatch
Solution: Columns must be same or compatible types – use CAST() to convert if needed.
3. Column Ambiguity
Error: Column title is ambiguous
Solution: Rename conflicting columns with aliases – like title AS video_title.
Let’s take an example fixing these errors:
Fixed UNION Query
SELECT date, title AS video_title, CAST(views AS INTEGER) AS engagement
FROM video_logs
UNION
SELECT date, title AS audio_title, listens
FROM audio_logs;
No more errors! Our column counts match, datatypes are consistent, and titles are properly aliased.
When to Avoid UNION
Despite its utility, UNION isn’t always the best approach.
Downsides of UNION:
- Performance overhead to remove duplicates
- More complex queries leading to slower execution
- Need to standardize column data types
In many cases, JOIN may be a better option for combining table data without extensive transformations.
Real-World Examples of SQLite UNION
Beyond simple demos, let’s explore some practical use cases leveraging UNION for business analytics:
1. Customer Lifetime Value Reporting
Goal: Calculate LTV across various revenue streams like purchases, subscriptions, ads displayed per customer.
We can UNION respective data slices and evaluate using analytical window functions:
WITH full_data AS (
SELECT user_id, SUM(purchase_amt) AS purchase_ltv
FROM purchase_table
GROUP BY user_id
UNION ALL
SELECT user_id, SUM(subscription_amt) AS subscription_ltv
FROM subscription_table
GROUP BY user_id
)
SELECT
user_id,
SUM(purchase_ltv) AS purchase_ltv,
SUM(subscription_ltv) AS subscription_ltv,
SUM(purchase_ltv + subscription_ltv) AS total_ltv
FROM full_data
GROUP BY user_id;
2. Store Inventory Lookup
Goal: Search current warehouse inventory plus outstanding vendor purchase orders.
UNION can provide a single virtual lookup without expensive joins:
SELECT sku, quantity FROM warehouse_stock
UNION
SELECT item_sku AS sku, quantity_ordered AS quantity
FROM vendor_purchase_orders;
This simplifies finding supply status for inventory planning.
The benefit of UNION here is avoiding complex correlated subqueries or joins to check multiple tables separately.
3. Dashboarding with Multiple Datasets
UNION can be handy for combining various charts or metrics into a single dashboard view.
We can build a cross-functional KPI dashboard blending related-but-separate data blocks:
SELECT period, revenue, 'Sales' AS metric FROM sales_table
UNION ALL
SELECT DATE, cost, 'Expenses' FROM costs_table
UNION ALL
SELECT week, count, 'Support Tickets' AS metric FROM ticket_table
ORDER BY period;
The unified output becomes an easy backend for rendering departmental metrics into a consolidated dashboard.
Key Takeaways
The UNION operator joins rows from multiple SELECT statements into a single result set. Key learnings:
- UNION eliminates duplicate records while UNION ALL allows duplicates
- Sort final output via ORDER BY clause applied post-UNION
- Corresponding columns must have matching data types
- Rule of thumb: Prefer UNION ALL for performance when possible
With UNION, you can skip complex joins and efficiently consolidate disparate data for simpler analysis.
Hopefully this guide has shown how SQLite UNION can level-up your analytical database skills! Let me know if you have any other questions.