Difference between Union and Union All operator

Distinction between Union and Union All Operator

Union and Union All operators exist in SQL. They serve to combine results of multiple SELECT queries. This merging provides a single result set. Yet, there is a vital distinction. It relates to their treatment of duplicate rows. The Union operator is the first one to consider.

Union Operator

The Union operator unites result sets of two or more SELECT queries. It does so into a singular result. The operator eliminates duplicate rows. It merges the results, then provides a distinct set of rows. As a result, any duplicates are removed.

Two SELECT queries, for example, can be merged using the UNION operator. The final result set will eliminate any duplicate rows after merging. Therefore, the returned set is distinct, with all duplicates removed.

Syntax:

SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2;

The UNION operator deletes duplicate rows from the joined result set. The remaining rows are distinct and non-duplicated. It orders and offers a set of distinct rows. The speed of the UNION operator is slower than UNION ALL due to an extra step that removes the duplicates.

UNION ALL Operator

The UNION ALL operator amalgamates result sets of two or more SELECT queries into a single result set. Even duplicate rows are included. UNION ALL brings in all rows from the combined result sets, including duplicates.

Syntax:

SELECT column1, column2, ... FROM table1 UNION ALL SELECT column1, column2, ... FROM table2;

The UNION ALL operator does not eliminate duplicate rows. Instead, it combines all rows from the result sets, including any duplicates. The operator is faster than UNION because it doesn't need to execute the additional step of removing duplicates.

Example

Let's look at two simple tables: Employees and Managers.

Table: Employees

EmployeeID	Name
1	Alice
2	Bob
3	Alice

Table: Managers

ManagerID	Name
101	David
1	Alice
103	Eve

Using UNION:

SELECT EmployeeID, Name FROM Employees UNION SELECT ManagerID, Name FROM Managers;

Result:

EmployeeID	Name
1	Alice
2	Bob
3	Alice
101	David
103	Eve

Using UNION ALL:

SELECT EmployeeID, Name FROM Employees UNION ALL SELECT ManagerID, Name FROM Managers;

Result:

EmployeeID	Name
1	Alice
2	Bob
3	Alice
101	David
1	Alice
103	Eve

Conclusion

In this example, UNION behaves differently from UNION ALL. When duplicate rows exist, UNION removes them. However, UNION ALL does not remove duplicates. Hence, if you require outputs without duplicates in your results, you may use UNION. For results with all existing duplicates, you can use UNION ALL.