Dofactory.com
Login Sign up
Dofactory.com
Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.

SQL UNION

UNION combines the result sets of two SELECT queries.

The data type and order of the columns in these queries must match.

SQL UNION returns only unique rows.

SQL UNION ALL allows duplicate rows to be present.

SQL Union

Example

#

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
Result:  120 records
Contact Phone Country
Patricio Simpson (1) 135-5555 Argentina
Sergio Gutiérrez (1) 123-5555 Argentina
Yvonne Moncada (1) 135-5333 Argentina
Ian Devling (03) 444-2343 Australia
Wendy Mackenzie (02) 555-5914 Australia
Georg Pipps 6562-9722 Austria

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

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, sorted by country. 
SELECT Country
  FROM Customer
UNION ALL
SELECT Country
  FROM Supplier
 ORDER BY Country
Result: 120 Records
Country
Argentina
Argentina
Argentina
Autralia
Autralia
Austria
Austria
Belgium
Belgium

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



Last updated on Dec 21, 2023

Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.
On this page