Understanding the UNION Keyword in SQL

The UNION keyword in SQL is a powerful operation that allows you to combine the results of two or more SELECT statements into a single result set. This operation is particularly useful when you want to retrieve data from multiple tables or queries that share a similar structure.

What is UNION?

In simpler terms, UNION takes the results from multiple SELECT queries and merges them into one comprehensive output. It ensures that the final result set contains only unique records, eliminating any duplicates. If you want to include duplicates, you can use UNION ALL, which retains all records from the combined queries.

A Practical Example

Let's say 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 list of all books in the library, regardless of their availability.

SQL Query Using UNION

To achieve this, you would use the UNION keyword as follows:

SQL

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

Show less code

Result of the Query:

This query combines the results from both tables, producing a unique list of all books in the library:

In this result set, you can see all books from both tables, with no duplicates.

Why Use UNION?

Using UNION is beneficial when you want to consolidate data from different sources that share a similar structure. In our example, it allows you to present a complete list of books in the library, whether they are available or checked out. This is particularly useful for reporting and analysis purposes.

Key Takeaways:

• UNION: Combines results from multiple SELECT statements into a single result set, removing duplicates.
• UNION ALL: Similar to UNION, but retains all records, including duplicates.
• Common Use Cases: Ideal for merging data from different tables, such as customer records from multiple regions, product listings from different suppliers, or any scenario where you need a comprehensive view of similar data.

Understanding how to use the UNION keyword effectively can enhance your data retrieval capabilities, allowing you to generate more insightful reports and analyses.

Happy querying!