SQL UNION ALL is a powerful tool for combining the results of multiple SELECT statements into a single result set. Unlike the UNION operator, which removes duplicate records, UNION ALL includes all duplicates. This makes UNION ALL a more efficient choice when duplicate records are acceptable or necessary.
Understanding SQL UNION ALL
The SQL UNION ALL command merges the results of two or more SELECT statements. It’s essential that all SELECT statements involved have the same number of columns, and the columns must be of compatible data types. If the number of columns or data types don’t match, an error will occur.
Syntax:
SELECT columns FROM table1
UNION ALL
SELECT columns FROM table2;SQL UNION ALL vs UNION
Here’s a comparison between UNION ALL and UNION:
| Feature | UNION ALL | UNION |
|---|---|---|
| Duplicate Records | Includes all duplicates | Removes duplicate records |
| Performance | Faster, as it doesn’t check for duplicates | Slower, due to duplicate elimination |
| Use Case | When duplicates are acceptable or needed | When duplicates need to be removed |
| Syntax | SELECT columns FROM table1 UNION ALL SELECT columns FROM table2; | SELECT columns FROM table1 UNION SELECT columns FROM table2; |
| Memory Usage | Generally lower, due to no extra processing for duplicates | Higher, due to additional steps for duplicate removal |
| Result Set | Combined rows from all SELECT statements, including duplicates | Combined rows from all SELECT statements, without duplicates |
| Applicability | Useful for large datasets where performance is critical and duplicates are acceptable | Useful when data integrity requires unique records in the result set |
Examples of SQL UNION ALL
Let’s look at some practical examples of the UNION ALL command.
Example 1: Single Field with Same Name
Suppose we want to combine the names from both the STUDENTS and TRIP_DETAIL tables, including all names, even if there are duplicates.
Tables:
STUDENTS Table:
| ROLL_NO | NAME | DOB | AGE |
|---|---|---|---|
| 1 | DEV SHARMA | 2001-08-16 | 17 |
| 2 | AMAN VERMA | 2002-01-04 | 16 |
| 3 | KRISH VATSA | 2000-11-29 | 18 |
TRIP_DETAIL Table:
| ROLL_NO | NAME | DOB | AGE |
|---|---|---|---|
| 1 | DEV SHARMA | 2001-08-16 | 17 |
| 2 | AMAN VERMA | 2002-01-04 | 16 |
| 3 | KRISH VATSA | 2000-11-29 | 18 |
| 4 | VARUN GOYAL | 2003-09-21 | 15 |
Query:
SELECT NAME FROM STUDENTS
UNION ALL
SELECT NAME FROM TRIP_DETAIL;Output:
| NAME |
|---|
| DEV SHARMA |
| AMAN VERMA |
| KRISH VATSA |
| DEV SHARMA |
| AMAN VERMA |
| KRISH VATSA |
| VARUN GOYAL |
Explanation:
The UNION ALL operator combines all rows from the NAME column in both tables. It includes all names, including duplicates. Names like “DEV SHARMA,” “AMAN VERMA,” and “KRISH VATSA” appear twice because they exist in both tables.
Example 2: Different Field Names
Suppose we want to combine the ROLL_NO from both tables and align the column names for consistency.
Query:
SELECT ROLL_NO AS Identifier FROM STUDENTS
UNION ALL
SELECT ROLL_NO AS Identifier FROM TRIP_DETAIL;Output:
| Identifier |
|---|
| 1 |
| 2 |
| 3 |
| 1 |
| 2 |
| 3 |
| 4 |
Explanation:
Here, ROLL_NO from both tables is selected and aliased as Identifier. The UNION ALL operator combines all roll numbers from both tables, including duplicates.
Important Points About SQL UNION ALL
- The UNION ALL command combines results from two or more SELECT statements from different tables.
- Unlike UNION, UNION ALL includes duplicate records from the SELECT statements.
- Both SELECT statements must have an equal number of columns, and these columns must be of compatible data types.
Conclusion
The UNION ALL operator is a valuable tool for combining results from multiple queries while preserving all rows, including duplicates. It is particularly useful when aggregating data from similar sources or when duplicates are acceptable. The examples provided illustrate how UNION ALL operates with columns of the same and different names.
FAQs
Can UNION ALL be used with different data types?
No, the columns being combined with UNION ALL must have compatible data types. If they differ, you may need to cast or convert the data types to make them compatible.
How does UNION ALL handle NULL values?
UNION ALL will include NULL values in the result set. If NULL values exist in the result sets of the queries being combined, they will appear in the final result set.
Can UNION ALL be used with more than two tables?
Yes, UNION ALL can combine results from more than two SELECT queries. You can chain multiple SELECT queries with UNION ALL to aggregate data from several sources.