UNION combining result sets in SQL

Introduction

UNION lets you combine rows from two or more tables into one list without duplicates. It helps you see all results together.

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

You need to combine sales data from two different years into a single report.

You want to merge email lists from two campaigns without repeats.

You want to get all unique product names from two different product tables.

Syntax

SQL

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

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

UNION removes duplicate rows. Use UNION ALL to keep duplicates.

Examples

Combine customer names from two regions, showing each name only once.

SQL

SELECT name FROM customers_north
UNION
SELECT name FROM customers_south;

List all unique products sold in 2022 and 2023.

SQL

SELECT product_id, product_name FROM products_2022
UNION
SELECT product_id, product_name FROM products_2023;

Combine two email lists including duplicates.

SQL

SELECT email FROM newsletter_list1
UNION ALL
SELECT email FROM newsletter_list2;

Sample Program

This example creates two fruit tables, inserts some fruits, then combines their names without duplicates.

SQL

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'), ('Fig');

SELECT name FROM fruits1
UNION
SELECT name FROM fruits2;

Important Notes

UNION sorts the result by default, so the output is ordered.

If you want to keep duplicates, use UNION ALL instead.

Column names in the final result come from the first SELECT statement.

Summary

UNION combines rows from multiple SELECT queries into one list.

It removes duplicate rows by default.

All SELECT queries must have the same number of columns with compatible types.