SQL UNION ALL

UNION ALL Operator is used to combine the results of two or more SELECT statements into a single result set. Unlike the UNION operator, which eliminates duplicate records and UNION ALL includes all duplicates. This makes UNION ALL it faster and more efficient when we don't need to remove duplicates.

In this article, we will learn the SQL UNION ALL operator, how it compares with UNION, and practical examples to demonstrate its usage in real applications.

SQL UNION ALL Operator

• The SQL UNION ALL command combines the result of two or more SELECT statements in SQL.
  
• For performing the UNION ALL operation, it is necessary that both the SELECT statements should have an equal number of columns/fields, otherwise, the resulting expression will result in an error.

Syntax:

The syntax for the SQL UNION ALL operation is:

SELECT columns FROM table1
UNION ALL
SELECT columns FROM table2;

Examples of SQL UNION ALL

Let's look at some examples of the UNION ALL command in SQL to understand its working. First, let's create a demo SQL database and tables on which UNION ALL will be performed.

Demo SQL Database

In this tutorial on the UNION ALL operator, we will use the following table in examples.

STUDENTS table:

TRIP_DETAIL Table:

Example 1: Single Field With Same Name

We want to combine the names from both the STUDENTS and TRIP_DETAIL tables, including all names, even if there are duplicates.

SELECT NAME FROM STUDENTS
UNION ALL
SELECT NAME FROM TRIP_DETAIL;

Output:

Explanation: The UNION ALL operator combines all rows from the NAME column in both tables. It includes all names, including duplicates. In this case, "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:

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. Each ROLL_NO from STUDENTS appears twice because it also exists in TRIP_DETAIL.

Important Points About SQL UNION All

• UNION ALL command helps us to combine results of two or more SELECT statements from different tables.
  
• The UNION ALL command includes the duplicate records from the SELECT statements whereas the UNION command does not include duplicate records otherwise both the commands are same.
  
• For performing the UNION ALL operation, it is necessary that both the SELECT statements should have equal number of columns otherwise the resulting expression will result in an error.

SQL UNION All vs UNION

Here is the comparison between UNION ALL and UNION Operator:

Conclusion

The UNION ALL operator is a powerful tool for combining results from multiple queries while preserving all rows, including duplicates. It is particularly useful when you need to aggregate data from similar sources or when duplicates are not a concern. The examples provided illustrate how UNION ALL works with columns of the same and different names.

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.