032 SQL UNION ALL

#Edu #SQL

The page explains the SQL UNION ALL operator, which is used to combine the results of two or more SELECT statements. Key points:

UNION ALL includes all rows from each SELECT statement, allowing duplicates (unlike UNION, which removes duplicates).
Requirements:
- Each SELECT must have the same number of columns.
- The columns must have similar data types.
- The columns must appear in the same order.
Syntax Example:
```
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
```
The resulting column names usually match those from the first SELECT statement.
Example provided: combining city data from "Customers" and "Suppliers" tables, showcasing how duplicate cities are included.
It also shows how to use WHERE with UNION ALL to filter data, such as fetching only German cities from both tables.
A quiz question confirms the primary purpose: to combine the result sets of two or more SELECT statements.

The page is part of W3Schools’ SQL tutorial series, using sample data and interactive examples for learning and practice.[1]

SQL UNION ALL Operator

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:

Every SELECT statement within UNION ALL 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 ALL Syntax

While the [UNION](https://www.w3schools.com/sql/sql_union.asp) operator removes duplicate values by default, the UNION ALL includes duplicate values:

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.

Demo Database

In this tutorial we will use the well-known Northwind sample database.

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

SQL UNION ALL Example

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

SELECT City FROM Customers  
UNION ALL  
SELECT City FROM Suppliers  
ORDER BY City;

SQL UNION ALL With WHERE

The following SQL statement returns the German cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

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