Using SQL UNION: Combining Data from Different Tables into a Single Result

💡 What Will You Learn in This Guide?

In this guide, you'll learn how to combine data from multiple tables into a single result set using SQL's UNION and UNION ALL set operators.
You will also see step by step how to manage duplicate records, filter by WHERE, and sort by ORDER BY.

⚙️ 1️⃣ Creating Sample Database and Tables

In the examples below, we will test the use of UNION by combining both sales and rental tables in a bookstore system.

Connect to MySQL

mysql -u genixnode_dev -p

It connects to the MySQL server with the username genixnode_dev.

Create Database

CREATE DATABASE kitap_evi;
USE kitap_evi;

Creates and activates a database called book_house.

Purchasing Table

CREATE TABLE satinalma (
  satinalma_id INT,
  musteri_ad VARCHAR(30),
  kitap_ad VARCHAR(40),
  tarih DATE,
  PRIMARY KEY (satinalma_id)
);

Keeps records of customers purchasing books.

Rental Table

CREATE TABLE kiralama (
  kiralama_id INT,
  musteri_ad VARCHAR(30),
  kitap_ad VARCHAR(40),
  baslangic_tarihi DATE,
  bitis_tarihi DATE,
  PRIMARY KEY (kiralama_id)
);

Keeps records of customers renting books.

Sample Data

INSERT INTO satinalma VALUES
(1, 'samet', 'Uçurtma Avcısı', '2022-10-01'),
(2, 'samet', 'Sefiller', '2022-10-04'),
(3, 'samet', 'Dönüşüm', '2022-09-23'),
(4, 'ayşe', 'Simyacı', '2022-07-23'),
(5, 'ayşe', 'Yüz Yıllık Yalnızlık', '2022-10-01'),
(6, 'can', 'Uçurtma Avcısı', '2022-04-15'),
(7, 'can', 'Simyacı', '2022-10-13'),
(8, 'can', 'Sefiller', '2022-10-19');

INSERT INTO kiralama VALUES
(1, 'samet', 'Simyacı', '2022-09-14', '2022-11-14'),
(2, 'samet', 'Sefiller', '2022-10-01', '2022-12-31'),
(3, 'samet', 'Yüz Yıllık Yalnızlık', '2022-10-01', '2022-12-01'),
(4, 'ayşe', 'Uçurtma Avcısı', '2022-09-03', '2022-09-18'),
(5, 'ayşe', 'Suç ve Ceza', '2022-09-27', '2022-12-05'),
(6, 'deniz', 'Uçurtma Avcısı', '2022-10-01', '2022-11-15'),
(7, 'deniz', 'Sefiller', '2022-09-08', '2022-11-17'),
(8, 'deniz', 'Dönüşüm', '2022-09-04', '2022-10-23');

8 sample records were added to each table.

---

🧩 2️⃣ Understanding the UNION Operator

UNION combines the results of two different SELECT queries and automatically removes duplicate rows.

Simple Example

SELECT musteri_ad FROM satinalma
UNION
SELECT musteri_ad FROM kiralama;

Lists unique customer names from both tables.

Result:

customer_name
samet
ayse
can
sea ​​

---

📏 3️⃣ UNION Rules

Rule	Description
🎯 Number of columns must match	Both SELECT queries should return the same number of columns.
🧩 Data type must be compatible	The data types of corresponding columns must be the same.
🔁 Column order must match	If columns are selected in a different order, the results will be mixed.

Faulty Example

SELECT musteri_ad, kitap_ad FROM satinalma
UNION
SELECT kitap_ad FROM kiralama;

⚠️ Error:

“The used SELECT statements have a different number of columns.”

---

🔍 4️⃣ Using UNION with WHERE and ORDER BY

It is possible to filter before merging and sort after.

Filtering with WHERE

SELECT kitap_ad FROM satinalma
WHERE musteri_ad = 'samet'
UNION
SELECT kitap_ad FROM kiralama
WHERE musteri_ad = 'samet';

He only brings the books Samet bought or rented.

Sorting with ORDER BY

SELECT kitap_ad FROM satinalma
WHERE musteri_ad = 'samet'
UNION
SELECT kitap_ad FROM kiralama
WHERE musteri_ad = 'samet'
ORDER BY kitap_ad;

Sorts the results list alphabetically.

🔁 5️⃣ UNION ALL: Protecting Duplicates

Sometimes you may want to show cases where the same book was both purchased and rented. In this case, UNION ALL is used.

SELECT kitap_ad FROM satinalma
WHERE tarih = '2022-10-01'
UNION ALL
SELECT kitap_ad FROM kiralama
WHERE baslangic_tarihi = '2022-10-01'
ORDER BY kitap_ad;

UNION ALL lists, preserving the same records.

Result:

book_name
The Kite Runner
One Hundred Years of Solitude
One Hundred Years of Solitude

---

💨 Performance Rating

UNION ALL is faster than UNION because it does not double check. If there is no duplicate data in your tables, choose UNION ALL.

---

❓ Frequently Asked Questions (FAQ)

1. What is the difference between UNION and JOIN?

UNION combines row-wise, JOIN associates column-wise.

2. How many tables can UNION combine?

As much as you want. Multiple SELECT queries can be chained.

3. According to which table are column names determined?

It is based on the column names in the first SELECT query.

4. Can I use UNION with different data types?

No, column types must be compatible. However, MySQL can automatically convert some types (e.g. VARCHAR-TEXT).

---

🚀 Result

With this guide, you learned to combine data from different tables using SQL UNION and UNION ALL operators. You can now check recurring data in reporting and create combined results.