SQL UNION – Mastering the UNION and UNION ALL Operators

Let’s understand two extremely useful SQL operators – UNION and UNION ALL. We will understand their syntax, when and why to use them, and of course, we’ll illustrate everything with examples.

What are SQL UNION and UNION ALL Operators?

The UNION and UNION ALL operators in SQL allow you to combine the results of two or more SELECT statements into a single result set. The key difference between them is that UNION removes duplicate records from the result set, while UNION ALL retains them.

The main rules to remember when using the UNION & UNION ALL operator are:

1. Each SELECT statement within the UNION & UNION ALL must have the same number of columns.
2. The corresponding columns in each SELECT statement must have similar data types.
3. The columns in each SELECT statement must also be in the same order.

Why is SQL UNION & UNION ALL Operator Important?

The UNION & UNION ALL operator is important when you need to combine data from multiple tables with similar structures but can’t join them on common columns. This is often the case when working with normalized data in a relational database. You can also use it to combine data from tables that store similar data in different periods.

Syntax of UNION and UNION ALL Operators

Before delving into examples, let’s understand the syntax.

For UNION:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

For UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Illustrative Examples

Imagine we have an online bookstore database with two tables: Current_Year_Sales and Previous_Year_Sales. Both have the same structure, storing Book_ID, Book_Name, and Author.

Current_Year_Sales Table

Book_ID    Book_Name            Author
1          'A Promised Land'   'Barack Obama'
2          'Greenlights'       'Matthew McConaughey'
3          'The Code Breaker'  'Walter Isaacson'

Previous_Year_Sales Table

Book_ID    Book_Name                   Author
1          'Becoming'                 'Michelle Obama'
2          'Where the Crawdads Sing'  'Delia Owens'
3          'A Promised Land'          'Barack Obama'

Let’s say we want to create a list of all unique books sold in the current and previous years.

UNION

Here, the UNION operator is our go-to tool:

SELECT * FROM Current_Year_Sales
UNION
SELECT * FROM Previous_Year_Sales;

The result would be:

Book_ID    Book_Name                  Author
1          'A Promised Land'          'Barack Obama'
2          'Greenlights'              'Matthew McConaughey'
3          'The Code Breaker'         'Walter Isaacson'
1          'Becoming'                 'Michelle Obama'
2          'Where the Crawdads Sing'  'Delia Owens'

Notice ‘A Promised Land‘ by Barack Obama, which appeared in both tables, is listed only once in the final result set.

UNION ALL

But what if we want to maintain all records, including duplicates? This is where UNION ALL comes in handy.

SELECT * FROM Current_Year_Sales
UNION ALL
SELECT * FROM Previous_Year_Sales;

The result set now is:

Book_ID    Book_Name                  Author
1          'A Promised Land'          'Barack Obama'
2          'Greenlights'              'Matthew McConaughey'
3          'The Code Breaker'         'Walter Isaacson'
1          'Becoming'                 'Michelle Obama'
2          'Where the Crawdads Sing'  'Delia Owens'
3          'A Promised Land'          'Barack Obama'

The book ‘A Promised Land‘ by Barack Obama now appears twice, reflecting its presence in both years’ sales data.

Wrap Up

The SQL UNION and UNION ALL operators are powerful tools for consolidating data from multiple SELECT statements into one result set. Mastering these operators will enable you to handle complex data consolidation scenarios with ease.