Bugl — code interview practice that feels like a game

Loading lesson path

The SQL UNION ALL Operator

The UNION ALL operator is used to combine the result-set of two or more SELECT statements.

The UNION ALL operator includes all rows from each statement, including any duplicates .

Requirements for UNION ALL

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

Note

The column names in the result-set are usually equal to the column names in the first SELECT statement.

Below is a selection from the "Customers" table:

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

And a selection from the "Suppliers" table:

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

The following SQL returns all the countries (also duplicate values) from both the "Customers" and the "Suppliers" table:

Example

 SELECT Country FROM Customers
UNION ALL
SELECT Country FROM Suppliers

 ORDER BY Country;

Here we add a WHERE clause to return all the German cities from both the "Customers" and the "Suppliers" table:

Example

 SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL

 SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;