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!

SQLite - UNIONS Clause (Bahasa Melayu)

Halo, para ahli pangkalan data masa depan! Hari ini, kita akan memulakan sebuah perjalanan menarik ke dunia SQLite dan menjelajahi klausa UNIONS yang magical. Sebagai guru komputer di lingkungan anda, saya akan menghidangkan anda dalam petualangan ini langkah demi langkah. Jadi, pegang wands virtual anda (papan kekunci), dan mari kita mula!

Apa Itu UNION?

Bayangkan anda ada dua senarai berasingan rasa es krim kegemaran anda - satu dari musim panas tahun lepas dan satu dari musim panas ini. Sekarang, apa jika anda mahu menggabungkan senarai ini menjadi satu senarai super semua rasa es krim kegemaran anda? Itu adalah apa yang UNION lakukan di SQLite, tetapi dengan jadual pangkalan data bukan rasa es krim!

Klausa UNION di SQLite membolehkan anda menggabungkan set hasil daripada dua atau lebih pernyataan SELECT. Ia seperti membuat satu parti besar di mana kumpulan data berbeza dapat bergaul dan membentuk satu kumpulan yang lebih besar.

Sintaks Asas

Sintaks asas klausa UNION adalah seperti ini:

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

mari kitaongkai ini:

Kita ada dua pernyataan SELECT.
Setiap pernyataan SELECT boleh mengquery jadual berbeza.
Kata UNION diletakkan di antara pernyataan ini, bertindak sebagai jambatan.

Rules Penting

Sebelum kita masuk ke contoh, mari kitaulas beberapa rules penting untuk menggunakan UNION:

Bilangan dan urutan column dalam semua pernyataan SELECT mestilah sama.
Jenis data column sepadan mestilah kompatibel.
Secara default, UNION menghapuskan baris duplikat (kita akan lihat bagaimana untuk mengubah ini kemudian).

UNION Dalam Tindakan

Mari kita buat beberapa jadual contoh dan lihat UNION dalam tindakan!

-- Buat dan isikan jadual 'employees'
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT
);

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

-- Buat dan isikan jadual 'contractors'
CREATE TABLE contractors (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT
);

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

Sekarang, mari kita gunakan UNION untuk menggabungkan jadual ini:

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

Query ini akan mengembalikan:

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

Apa yang terjadi di sini? Klausa UNION menggabungkan hasil dari kedua-dua jadual, memberikan kita senarai lengkap semua pekerja (pekerja dan kontraktor) tanpa sebarang duplikat.

.Magic Deduplication

Adakah anda melihat sesuatu yang menarik? Walaupun kita ada dua orang di departemen HR (Alice dan Frank), mereka keduanya muncul dalam hasil kita. Ini kerana UNION secara default menghapuskan baris duplikat berdasarkan semua nilai column.

Jika kita ada seseorang dengan nama dan departemen yang sama dalam kedua-dua jadual, UNION hanya akan menunjukkan mereka sekali. Sebagai contoh:

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

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

Ini masih akan memberikan kita hasil yang sama seperti sebelumnya, tanpa 'Alice' ekstra di HR.

Klausa UNION ALL

kadang-kadang, anda mungkin mahu menyimpan semua baris, bahkan jika mereka adalah duplikat. Itu di mana UNION ALL berguna. Ia seperti memberitahu pangkalan data, "Saya mahu semua orang di parti ini, bahkan jika mereka mempunyai kembar!"

Mari kita modifikasi query sebelum ini:

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

Sekarang kita mendapatkan:

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

Lihat bagaimana kita sekarang ada dua entri untuk Alice di HR? UNION ALL menyimpan semua baris, tanpa menghiraukan duplikasi.

Aplikasi Praktikal

UNION dan UNION ALL bukan hanya untuk hiburan - mereka mempunyai aplikasi dunia nyata! Berikut adalah beberapa senario di mana anda mungkin menggunakan mereka:

Menggabungkan data dari beberapa jadual dengan struktur yang hampir sama (seperti contoh pekerja dan kontraktor kita).
Membuat laporan yang meliputi periode waktu berbeza atau kategori.
Menggabungkan data dari pangkalan data berbeza atau sumber data.

Mari kita lihat contoh yang lebih kompleks. Bayangkan kita menjalankan sebuah kedai buku dan ingin melihat semua transaksi, baik itu pembelian atau pengembalian:

-- Buat dan isikan jadual 'purchases'
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');

-- Buat dan isikan jadual 'returns'
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');

-- Gabungkan pembelian dan pengembalian
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;

Query ini akan memberikan kita:

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

Di sini, kita telah menggunakan UNION ALL untuk menggabungkan pembelian dan pengembalian, menambahkan column untuk membezakan jenis transaksi, dan mengurutkan hasilnya menurut tarikh.

Tips dan Trik

Mengurutkan Hasil: Anda boleh menambah klausa ORDER BY di akhir query UNION untuk mengurutkan hasil gabungan.
Penyaringan Hasil Gabungan: Gunakan klausa WHERE di setiap pernyataan SELECT untuk menyaring hasil masing-masing jadual, atau wrap query UNION anda dalam subquery dan gunakan klausa WHERE untuk menyaring hasil gabungan.
Alias Column: Jika column anda mempunyai nama yang berbeza dalam jadual berbeza, anda boleh menggunakan alias untuk membuat mereka sepadan:

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

Menggabungkan Lebih Dari Dua Jadual: Anda tidak terbatas kepada hanya dua jadual - anda boleh rantai banyak klausa UNION atau UNION ALL untuk menggabungkan banyak jadual.

Metode Umum

Berikut adalah tabel yang menggabungkan metode umum berkaitan dengan UNION di SQLite:

Metode	Deskripsi	Contoh
UNION	Menggabungkan hasil daripada dua atau lebih pernyataan SELECT dan menghapuskan duplikat	`SELECT * FROM table1 UNION SELECT * FROM table2`
UNION ALL	Menggabungkan hasil daripada dua atau lebih pernyataan SELECT dan menyimpan semua baris, termasuk duplikat	`SELECT * FROM table1 UNION ALL SELECT * FROM table2`
ORDER BY	Digunakan dengan UNION untuk mengurutkan hasil gabungan	`(SELECT * FROM table1 UNION SELECT * FROM table2) ORDER BY column_name`
WHERE	Digunakan dalam pernyataan SELECT individual untuk menyaring hasil sebelum penggabungan	`SELECT * FROM table1 WHERE condition UNION SELECT * FROM table2 WHERE condition`

Dan di situ anda ada, rakan-rakan! Anda telah meningkatkan kemahiran SQLite anda dengan menguasai klausa UNION. Ingat, latihan membuat sempurna, jadi jangan takut untuk mencoba query ini pada dataset anda sendiri. Selamat mencari, dan mayat UNION anda selalu berjaya!

Credits: Image by storyset