SQL UNION, SQL UNION ALL

SQL UNION versus SQL UNION ALL

UNION combines the result sets of 2 or more queries.

Column data types in these queries must match.

SQL UNION returns only unique rows.

SQL UNION ALL allows duplicate rows to be present.

Example

#

Problem: List all contacts (customers and suppliers) and their phone numbers, sorted by country.

copy

SELECT FirstName + ' ' + LastName AS Contact, Phone, Country
  FROM Customer
UNION
SELECT ContactName, Phone, Country
  FROM Supplier
 ORDER BY Country

SELECT FirstName + ' ' + LastName AS Contact, Phone, Country
  FROM Customer
UNION
SELECT ContactName, Phone, Country
  FROM Supplier
 ORDER BY Country

SQL Delete Join

SQL Subquery

Syntax

#

UNION syntax.

SELECT column-names1
  FROM table-name1
 UNION
SELECT column-names2
  FROM table-name2

UNION ALL syntax.

SELECT column-names1
  FROM table-name1
 UNION ALL
SELECT column-names2
  FROM table-name2

More Examples

#

SQL UNION

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

Problem: List all unique countries for customers and suppliers.

copy

SELECT Country
  FROM Customer
UNION
SELECT Country
  FROM Supplier

SELECT Country
  FROM Customer
UNION
SELECT Country
  FROM Supplier

Result: 25 Records

Country
Argentinia
Australia
Austria
Belgium

SQL UNION ALL

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

Problem: List all countries for customers and suppliers, including duplicates.

copy

SELECT Country
  FROM Customer
UNION ALL
SELECT Country
  FROM Supplier

SELECT Country
  FROM Customer
UNION ALL
SELECT Country
  FROM Supplier

Result: 120 Records

Country
Germany
Mexico
Mexico
UK

SQL UNION Multiple Columns

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

Problem: List all contacts with full details, both customers and suppliers.

copy

SELECT 'Customer' As Type, 
       FirstName + ' ' + LastName AS ContactName, 
       City, Country, Phone
  FROM Customer
UNION
SELECT 'Supplier', 
       ContactName, City, Country, Phone
  FROM Supplier

SELECT 'Customer' As Type, 
       FirstName + ' ' + LastName AS ContactName, 
       City, Country, Phone
  FROM Customer
UNION
SELECT 'Supplier', 
       ContactName, City, Country, Phone
  FROM Supplier

Result: 120 Records

Type	ContactName	City	Country	Phone
Customer	Alejandra Camino	Madrid	Spain	(91) 745 6200
Customer	Alexander Feuer	Leipzig	Germany	0342-023176
Customer	Ana Trujillo	México D.F.	Mexico	(5) 555-4729
Customer	Anabela Domingues	Sao Paulo	Brazil	(11) 555-2167

Supplier	Anne Heikkonen	Lappeenranta	Finland	(953) 10956
Supplier	Antonio del Valle Saavedra	Oviedo	Spain	(98) 598 76 54
Supplier	Beate Vileid	Sandvika	Norway	(0)2-953010
Supplier	Carlos Diaz	Sao Paulo	Brazil	(11) 555 4640
Supplier	Chandra Leka	Singapore	Singapore	555-8787
Supplier	Chantal Goulet	Ste-Hyacinthe	Canada	(514) 555-2955
Supplier	Charlotte Cooper	London	UK	(171) 555-2222