Union All operator

Union All Operator in SQL

UNION ALL in SQL merges result sets from two or more SELECT queries into one. All rows appear. This includes duplicates. Let's give an analogy using a real-life store's inventory.

Real-Life Analogy: Store's Inventory

Picture yourself working in a retail store. The store has two distinct sections. There's one for clothing. There's another for accessories. Your task is to create a report. This report needs to list every item in both sections. This is for inventory management.

Using UNION ALL:

SELECT ItemID, ItemName, 'Clothing' AS ItemType FROM Clothing
UNION ALL
SELECT ItemID, ItemName, 'Accessories' AS ItemType FROM Accessories;

In this analogy:

• The Clothing segment emulates the first SELECT query.
• The Accessories segment is akin to the second SELECT query.
• ItemID and ItemName become columns in a database.
• ItemType is a virtual column. It signifies if the item is from clothing or accessories part.

Result:

ItemID	ItemName	ItemType
101	T-Shirt	Clothing
102	Jeans	Clothing
201	Sunglasses	Accessories
202	Watches	Accessories

In this example:

• UNION ALL operator fuses all items from both clothing as well as accessories sections. This includes duplicates.
• The table that results lists all the items. It also shows the section to which they belong.

Summing up, just as combining inventory data from varying sections in a store helps get an all-inclusive list, UNION ALL is akin to that. It lets you combine results of two or more queries. It includes all rows making a comprehensive result set.