Union & Merge

---

This section will cover methods to concatenate, and to rbind (if you’re familiar with R) - What’s important to remember is that UNION will rbind, in other words it’ll merge vertically, it’ll add rows that pass the criteria vertically, so we end up with the same width object but the length will vary.

Merge

---

Concat

Function	Use	Example	Result
CONCAT	Concatenate strings to create new text strings	CONCAT(‘Google’, ‘.com’)	Google.com
CONCAT_WS	Concatenate two or more strings together with a separator between each string	CONCAT_WS(‘ . ‘, ‘www’, ‘google’, ‘com’)	www.google.com
|| & +	Concatenate two or more strings together with the || operator	‘Google’ || ‘.com’	Google.com

Let’s start with:

CONCAT(string1, string2, ...., string_n)

CONCAT lets you add strings together to create new text strings that can be used as unique keys.

SELECT
     product_code,
     product_color,
     product,
     CONCAT(product_code,product_color) AS new_product_code
FROM
     MyTable
WHERE
     product = 'couch'
     
#______ OR ______
SELECT
    usertype,
    CONCAT (start_station_name," to ", end_station_name) AS route, 

Concat_ws

CONCAT_WS(separator, string1, string2, ...., string_n)

 CONCAT with Separator, like CONCAT but it includes a separator, such as a space or period, between the strings.

SELECT CONCAT_WS('.', 'www', 'schools', 'com');

||

|| can be used to concatenate strings on the fly, just place the || in between the strings, add your own separators if need be. Can only be used in certain SQL environment

SELECT   book_name || ' - ' || edition_name  AS full_book_title
FROM     MyTable

+

In some SQL environment you cannot use || but can use + instead (such as Microsoft SQL)

SELECT  'Google' + '.com'

Union

---

The UNION operator is used to combine the result-set of two or more SELECT statements. See How To Union for examples

• Every SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in every SELECT statement must also be in the same order
• UNION selects only distinct values, so if some rows have the same value in a column it will only choose one row
• Syntax

SELECT   column_name(s)
FROM     table1
UNION
SELECT   column_name(s)
FROM     table2;

WHERE    conditions
ORDER BY conditions

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
• Syntax:

SELECT   column_name(s)
FROM     table1
UNION ALL
SELECT   column_name(s)
FROM     table2;

WHERE    conditions
ORDER BY conditions

• 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

with conditons

• 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