SQL Basics

SQL Functions

SQL Joins

SQL Advanced

SQL Database

SQL Tables

SQL Constraints

SQL Performance

SQL Data Types

SQL Security

SQL UNION ALL

❮ Previous Next ❯

The SQL UNION ALL Operator

The UNION ALL operator is used to combine the result-set of two or more SELECT statements, including all duplicate values.

The same rules that apply to UNION also apply to UNION ALL:

UNION ALL Syntax

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Demo Database

Customers Table:

City Country
Berlin Germany
México D.F. Mexico

Suppliers Table:

City Country
London UK
New Orleans USA
Berlin Germany

UNION ALL Example

The following SQL statement returns the cities (including duplicates) from both the "Customers" and the "Suppliers" table.

UNION ALL Example

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

The result set would look like this:

City
Berlin
Berlin
London
México D.F.
New Orleans

Notice that "Berlin" appears twice because it exists in both tables, and UNION ALL includes all values. If we had used UNION, "Berlin" would have appeared only once.

❮ Previous Next ❯