SQL UNION

What does a SQL UNION statement do?

UNION combines the result sets of two different queries.
Column data types in the two queries must match.
A UNION combines the results by column position rather than column name.

The SQL UNION syntax

The general syntax is

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

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

SQL UNION Examples

Problem: List all companies,
including suppliers and customers.

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

Result:

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