SQLite - UNIONS Clause

Hello, future database wizards! Today, we're going to embark on an exciting journey into the world of SQLite and explore the magical UNIONS clause. As your friendly neighborhood computer teacher, I'll guide you through this adventure step by step. So, grab your virtual wands (keyboards), and let's get started!

What is a UNION?

Imagine you have two separate lists of your favorite ice cream flavors - one from last summer and one from this summer. Now, what if you wanted to combine these lists into one super list of all your favorite flavors? That's essentially what a UNION does in SQLite, but with database tables instead of ice cream flavors!

A UNION clause in SQLite allows you to combine the result sets of two or more SELECT statements. It's like creating a big party where different groups of data can mingle and form a new, larger group.

Basic Syntax

The basic syntax of a UNION looks like this:

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

Let's break this down:

We have two SELECT statements.
Each SELECT statement can query different tables.
The UNION keyword sits between these statements, acting as a bridge.

Important Rules

Before we dive into examples, let's go over some crucial rules for using UNION:

The number and order of columns in all SELECT statements must be the same.
The data types of corresponding columns should be compatible.
By default, UNION removes duplicate rows (we'll see how to change this later).

UNION in Action

Let's create some example tables and see UNION in action!

-- Create and populate the 'employees' table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT
);

INSERT INTO employees (name, department) VALUES
    ('Alice', 'HR'),
    ('Bob', 'IT'),
    ('Charlie', 'Finance');

-- Create and populate the 'contractors' table
CREATE TABLE contractors (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT
);

INSERT INTO contractors (name, department) VALUES
    ('David', 'Marketing'),
    ('Eve', 'IT'),
    ('Frank', 'HR');

Now, let's use UNION to combine these tables:

SELECT name, department FROM employees
UNION
SELECT name, department FROM contractors;

This query will return:

Alice    HR
Bob      IT
Charlie  Finance
David    Marketing
Eve      IT
Frank    HR

What happened here? The UNION clause combined the results from both tables, giving us a complete list of all workers (employees and contractors) without any duplicates.

The Magic of Deduplication

Did you notice something interesting? Even though we have two people in the HR department (Alice and Frank), they both appear in our result. This is because UNION automatically removes duplicate rows based on all column values.

If we had someone with the exact same name and department in both tables, UNION would only show them once. For example:

INSERT INTO contractors (name, department) VALUES ('Alice', 'HR');

SELECT name, department FROM employees
UNION
SELECT name, department FROM contractors;

This would still give us the same result as before, without an extra 'Alice' in HR.

The UNION ALL Clause

Sometimes, you might want to keep all rows, even if they're duplicates. That's where UNION ALL comes in handy. It's like telling the database, "I want everyone at this party, even if they have a twin!"

Let's modify our previous query:

SELECT name, department FROM employees
UNION ALL
SELECT name, department FROM contractors;

Now we get:

Alice    HR
Bob      IT
Charlie  Finance
David    Marketing
Eve      IT
Frank    HR
Alice    HR

See how we now have two entries for Alice in HR? UNION ALL keeps all rows, regardless of duplication.

Practical Applications

UNION and UNION ALL aren't just for fun – they have real-world applications! Here are a few scenarios where you might use them:

Combining data from multiple tables with similar structures (like our employees and contractors example).
Creating reports that span different time periods or categories.
Merging data from different databases or data sources.

Let's look at a more complex example. Imagine we're running a bookstore and want to see all transactions, whether they're purchases or returns:

-- Create and populate the 'purchases' table
CREATE TABLE purchases (
    id INTEGER PRIMARY KEY,
    book_title TEXT,
    amount DECIMAL(10, 2),
    transaction_date DATE
);

INSERT INTO purchases (book_title, amount, transaction_date) VALUES
    ('The Great Gatsby', 15.99, '2023-06-01'),
    ('To Kill a Mockingbird', 12.50, '2023-06-02'),
    ('1984', 10.99, '2023-06-03');

-- Create and populate the 'returns' table
CREATE TABLE returns (
    id INTEGER PRIMARY KEY,
    book_title TEXT,
    amount DECIMAL(10, 2),
    transaction_date DATE
);

INSERT INTO returns (book_title, amount, transaction_date) VALUES
    ('The Great Gatsby', -15.99, '2023-06-05'),
    ('Pride and Prejudice', -14.99, '2023-06-06');

-- Combine purchases and returns
SELECT book_title, amount, transaction_date, 'Purchase' as transaction_type
FROM purchases
UNION ALL
SELECT book_title, amount, transaction_date, 'Return' as transaction_type
FROM returns
ORDER BY transaction_date;

This query will give us:

The Great Gatsby       15.99   2023-06-01  Purchase
To Kill a Mockingbird  12.50   2023-06-02  Purchase
1984                   10.99   2023-06-03  Purchase
The Great Gatsby      -15.99   2023-06-05  Return
Pride and Prejudice   -14.99   2023-06-06  Return

Here, we've used UNION ALL to combine purchases and returns, added a column to distinguish between transaction types, and ordered the results by date.

Tips and Tricks

Ordering Results: You can add an ORDER BY clause at the end of your UNION query to sort the combined results.
Filtering Combined Results: Use a WHERE clause in each SELECT statement to filter individual tables, or wrap your UNION query in a subquery and apply a WHERE clause to filter the combined results.
Column Aliases: If your columns have different names in different tables, you can use aliases to make them match:

SELECT name AS person, department FROM employees
UNION
SELECT contractor_name AS person, dept AS department FROM contractors;

Combining More Than Two Tables: You're not limited to just two tables - you can chain multiple UNION or UNION ALL clauses to combine many tables.

Common Methods

Here's a table summarizing the common methods related to UNION in SQLite:

Method	Description	Example
UNION	Combines results of two or more SELECT statements and removes duplicates	`SELECT * FROM table1 UNION SELECT * FROM table2`
UNION ALL	Combines results of two or more SELECT statements and keeps all rows, including duplicates	`SELECT * FROM table1 UNION ALL SELECT * FROM table2`
ORDER BY	Used with UNION to sort the combined results	`(SELECT * FROM table1 UNION SELECT * FROM table2) ORDER BY column_name`
WHERE	Used in individual SELECT statements to filter results before combining	`SELECT * FROM table1 WHERE condition UNION SELECT * FROM table2 WHERE condition`

And there you have it, folks! You've just leveled up your SQLite skills by mastering the UNION clause. Remember, practice makes perfect, so don't be afraid to experiment with these queries on your own datasets. Happy querying, and may your UNIONs always be successful!

Credits: Image by storyset