Union

Distinct Values

Union

  • When we only want to merge and use DISTINCT values in a column use UNION
  • Here we want to join the two tables then
  • Order by City
  • Will give us a column of all the unique cities in Alphabetical order

Customers:

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

Suppliers:

SupplierID SupplierName ContactName Address City PostalCode Country
1 Exotic Liquid Charlotte Cooper 49 Gilbert St. London EC1 4SD UK
2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA
3 Grandma Kelly’s Homestead Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA
  • Return the cities (only distinct values) from both Customers and Suppliers
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Output:

Number of Records: 94

City
Aachen 
Albuquerque 
Anchorage 
Ann Arbor 
Annecy 

Union All

  • Differentiating from UNION, UNION ALL allows duplicate values
  • Note: The column names in the result-set are usually equal to the column names in the first SELECT statement
  • Here we want all the cities including duplicate values (not sure why we’d want that)
  • Order by city as well
  • Returns the cities (duplicates included) from Customers and Suppliers
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

Output:

Number of Records: 120

City
Aachen 
Albuquerque 
Anchorage 
Ann Arbor 
Annecy 

Union All & Where

  • Here is an example where we want to make use of duplicates
  • Let’s say we want to extract all the orders, or the customers or in this case
  • The cities from both tables including duplicates
  • German cities (duplicate values also) from both Customers and Suppliers and order by city
SELECT      City, Country 
FROM        Customers
WHERE       Country='Germany'
UNION ALL
SELECT      City, Country 
FROM        Suppliers
WHERE       Country='Germany'
ORDER BY    City;

Output:

Number of Records: 14

City Country
Aachen  Germany 
Berlin  Germany 
Berlin  Germany 

Unique Type

  • As it is Customers table has a column labeled Customers
  • Supplier table has a column labeled Supplier
  • We want to list all the different types of client we had whether it’s a customer or supplier
  • So we use an ALIAS to assign the column Customers to Type so when the values (of customer or supplier) appears in the column labeled Type it would make more sense after we merge the two tables together
SELECT  'Customer' AS Type, ContactName, City, Country
FROM    Customers
UNION
SELECT  'Supplier', ContactName, City, Country
FROM    Suppliers;

Number of Records: 120

Type ContactName City Country
Customer  Alejandra Camino  Madrid  Spain 
Customer  Alexander Feuer  Leipzig  Germany 
Customer  Ana Trujillo  México D.F.  Mexico 
Customer  Anabela Domingues  São Paulo  Brazil 
Customer  André Fonseca  Campinas  Brazil