UNION and UNION ALL in MySQL

Introduction

UNION and UNION ALL combine results from two or more queries into one list. They help you see data from different places together.

You want to list all customers from two different regions in one list.

You need to combine product names from two tables to see everything available.

You want to merge sales records from two months into one report.

You want to get all email addresses from two contact lists, including duplicates.

You want to combine unique job titles from two departments.

Syntax

MySQL

SELECT column1, column2 FROM table1
UNION [ALL]
SELECT column1, column2 FROM table2;

UNION removes duplicate rows by default.

UNION ALL keeps all rows, including duplicates.

Examples

Combines customer names from north and south regions, removing duplicates.

MySQL

SELECT name FROM customers_north
UNION
SELECT name FROM customers_south;

Lists all products from both stores, including duplicates.

MySQL

SELECT product FROM store_a
UNION ALL
SELECT product FROM store_b;

Shows unique cities where offices are located in Europe and Asia.

MySQL

SELECT city FROM offices_europe
UNION
SELECT city FROM offices_asia;

Sample Program

This example creates two fruit tables and combines their names using UNION to show unique fruits.

MySQL

CREATE TABLE fruits1 (name VARCHAR(20));
CREATE TABLE fruits2 (name VARCHAR(20));

INSERT INTO fruits1 VALUES ('Apple'), ('Banana'), ('Cherry');
INSERT INTO fruits2 VALUES ('Banana'), ('Date'), ('Apple');

SELECT name FROM fruits1
UNION
SELECT name FROM fruits2;

Important Notes

All SELECT statements must have the same number of columns and compatible data types.

UNION sorts the result by default, which can be slower on large data sets.

Use UNION ALL if you want faster results and don't mind duplicates.

Summary

UNION combines results and removes duplicates.

UNION ALL combines results and keeps duplicates.

Use UNION when you want unique rows; use UNION ALL to keep all rows.