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:
- Each SELECT statement within the
UNION
&UNION ALL
must have the same number of columns. - The corresponding columns in each SELECT statement must have similar data types.
- 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.