How to Use "UNION" and "UNION ALL" in SQL?

In SQL, the UNION and UNION ALL operators are essential tools for combining the results of multiple SELECT queries into a single result set.

While both operators merge data from different tables or queries, they have distinct behaviors and use cases.

This article will explore the purpose of UNION and UNION ALL, their syntax, differences, and provide practical examples of how to use them effectively.

Table of Contents

  1. Introduction to UNION and UNION ALL
  2. Syntax of the UNION Operator
  3. Syntax of the UNION ALL Operator
  4. Differences between UNION and UNION ALL
  5. Using UNION and UNION ALL with Examples
  6. Conclusion

Introduction to UNION and UNION ALL

The UNION and UNION ALL operators in SQL enable you to combine the result sets of multiple SELECT queries into a single, merged result set.

These operators are often used when you need to consolidate data from different tables, ensuring that duplicates are removed (in the case of UNION) or retained (in the case of UNION ALL).

Syntax of the UNION Operator

The basic syntax of the UNION operator is as follows:

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

The UNION operator merges the result sets of the two SELECT queries and returns a distinct result set, eliminating duplicate rows.

Syntax of the UNION ALL Operator

The basic syntax of the UNION ALL operator is similar to UNION, but it retains all rows, including duplicates:

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

The UNION ALL operator combines the result sets of the two SELECT queries and preserves duplicate rows.

Differences between UNION and UNION ALL

The primary difference between UNION and UNION ALL lies in how they handle duplicate rows:

Because UNION removes duplicates, it may have a slightly higher performance cost as it requires the database to perform additional operations to identify and eliminate duplicates. On the other hand, UNION ALL is generally faster because it simply concatenates the result sets.

Using UNION and UNION ALL with Examples

Let's explore some practical examples of using UNION and UNION ALL:

Example 1: Using UNION

Suppose you have two tables, "Customers" and "Suppliers," and you want to create a list of all unique company names from both tables.

You can use the UNION operator:

SELECT CompanyName
FROM Customers
UNION
SELECT CompanyName
FROM Suppliers;

This SQL statement combines the company names from both tables while removing duplicates, resulting in a list of unique company names.

Example 2: Using UNION ALL

Consider a scenario where you have two tables, "Orders" and "ArchivedOrders," and you want to combine all orders, including those archived, into a single list.

To retain all orders, including any duplicates, you can use the UNION ALL operator:

SELECT OrderID, OrderDate
FROM Orders
UNION ALL
SELECT OrderID, OrderDate
FROM ArchivedOrders;

This SQL statement combines all orders from both tables, including duplicates, into a single list.

Conclusion

The UNION and UNION ALL operators in SQL provide powerful means to combine the results of multiple SELECT queries into a single, merged result set.

Understanding their differences is essential, as UNION removes duplicates, while UNION ALL retains them. These operators are commonly used to consolidate data from different tables or queries, making them invaluable tools in SQL for data integration and analysis.