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.

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

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.
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.
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.
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

You may also like



Guides