SQL - UNION ALL

What is UNION ALL?

UNION ALL is a SQL operation that combines the results of two or more SELECT queries into a single result set. Unlike the UNION operator, which removes duplicate records from the result, UNION ALL includes all records from the combined queries, even if they are duplicates. This makes UNION ALL a useful tool when you want to retain all data, including repeated entries.

In simpler terms, UNION ALL gives you everything from the combined queries without filtering out any duplicates.

A Practical Example

Imagine you are managing a library database with two tables: one for books currently available and another for books that are checked out.

Available Books Table:

Checked Out Books Table:

You want to create a comprehensive list of all books in the library, including both available and checked out books, even if some titles appear in both tables.

SQL Query Using UNION ALL

To achieve this, you would use UNION ALL:

SQL

SELECT BookID, Title, Author
FROM AvailableBooks
UNION ALL
SELECT BookID, Title, Author
FROM CheckedOutBooks;

Show less code

Result of the Query:

This query retrieves all books from both tables, including duplicates. The output would look like this:

Here, "The Great Gatsby" appears twice because it is present in both the available and checked out books tables.

Why Use UNION ALL?

UNION ALL is particularly useful when you want to ensure that no data is lost during the combination of results. In our example, you may want to see how many times a book appears in the library, regardless of its status. If you used UNION instead, the duplicate entry for "The Great Gatsby" would be eliminated, and you would lose valuable information about its availability.

Key Takeaways:

• UNION ALL: Combines results from multiple SELECT queries, including duplicates.
• Useful for Data Completeness: Retains all records, providing a full picture of the data across multiple tables.
• Common Use Cases: Ideal for aggregating data from different sources, such as sales records from different regions, customer lists from various campaigns, or any scenario where you want to see all entries without filtering out duplicates.

Understanding how to use UNION ALL effectively can enhance your data analysis capabilities, allowing you to create more comprehensive reports and insights.

Happy querying!