SQLite UNION: Combining Result Sets

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

audio_logs

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:

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:

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:

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.