SQL UNION

What does a SQL UNION statement do?

UNION combines the result sets of two queries.
Column data types in the two queries must match.
UNION combines 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