In the realm of MySQL, the UNION ALL operator is a powerful tool for combining results from multiple SELECT statements. Unlike the UNION operator, which removes duplicate rows, UNION ALL retains all rows from the combined queries, including duplicates. This can be particularly useful when you need a complete set of results from various sources or when performance is a concern.
Description
The UNION ALL operator is employed to merge the results of two or more SELECT queries into a single result set. Each SELECT statement included in the UNION ALL must return the same number of columns, and the corresponding columns must be of similar data types. Importantly, UNION ALL does not filter out duplicate rows, which can make it a better choice for scenarios where retaining all entries is crucial.
Syntax
The basic syntax for the UNION ALL operator is:
sql
SELECT column1, column2, ..., column_n
FROM table1
[WHERE conditions]
UNION ALL
SELECT column1, column2, ..., column_n
FROM table2
[WHERE conditions];
column1, column2, ..., column_n: These are the columns or expressions you wish to retrieve from eachSELECTstatement.table1andtable2: The tables from which you want to retrieve the records. You can list more tables in additionalSELECTstatements.[WHERE conditions]: Optional conditions that filter the records in eachSELECTstatement.
Key Points
- Column Consistency: Each
SELECTstatement must have the same number of columns in the result set. Columns should also be in the same order and have compatible data types. - Performance:
UNION ALLis generally faster thanUNIONbecause it doesn’t require the database to sort and remove duplicates. This can be advantageous in performance-sensitive applications.
Example Usage
Consider a sample database like Northwind, which includes tables such as Customers and Suppliers. Let’s say you want to combine the names and contact information from both tables into a single result set.
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 |
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 |
To combine these tables using UNION ALL, you can execute:
sql
SELECT CustomerName AS Name, ContactName, Address, City, Country
FROM Customers
UNION ALL
SELECT SupplierName AS Name, ContactName, Address, City, Country
FROM Suppliers;
This query merges the names and contact details from both customers and suppliers into one unified result set, preserving all records from both tables, including duplicates if any.
Conclusion
The UNION ALL operator in MySQL is a versatile tool for aggregating results from multiple SELECT statements without eliminating duplicate entries. By understanding its syntax and characteristics, you can effectively utilize it in scenarios where retaining complete data sets is important, and you can benefit from its potential performance advantages over UNION. Whether working with customer and supplier data, as illustrated, or other combined data scenarios, UNION ALL provides a straightforward method for comprehensive data retrieval.
