UNION ALL with duplicates in SQL

Introduction

Use UNION ALL to combine results from two or more queries and keep all rows, including duplicates.

You want to combine customer lists from two different stores and keep all entries, even if some customers appear in both.

You need to merge sales data from two months and want to see every sale, including repeated ones.

You want to combine two lists of products from different suppliers and keep all products, even if some are the same.

You want to add new records to an existing list without removing duplicates.

Syntax

SQL

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

UNION ALL keeps all rows from both queries, including duplicates.

Columns selected in both queries must match in number and type.

Examples

Combines names from employees and contractors, showing all names even if repeated.

SQL

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

Lists all products and prices from two stores, including duplicates.

SQL

SELECT product_id, price FROM store1
UNION ALL
SELECT product_id, price FROM store2;

Sample Program

This example creates two tables with fruit names. The query combines all fruits from both tables, including duplicates like 'Apple' and 'Banana'.

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

SELECT name FROM fruits1
UNION ALL
SELECT name FROM fruits2;

Important Notes

UNION ALL is faster than UNION because it does not check for duplicates.

Use UNION if you want to remove duplicates instead.

Summary

UNION ALL combines results from multiple queries and keeps duplicates.

Both queries must select the same number of columns with compatible types.

Use UNION ALL when you want to keep all rows without filtering duplicates.