MySQL - UNION vs UNION ALL

Hello, future database wizards! Today, we're going to dive into the fascinating world of MySQL and explore two powerful operators: UNION and UNION ALL. By the end of this tutorial, you'll be combining data like a pro! So, grab your favorite beverage, and let's get started!

MySQL UNION Operator

What is UNION?

UNION is like a magical wand in MySQL that allows us to combine the results of two or more SELECT statements. It's like inviting different groups of friends to one big party – everyone comes together, but duplicates are politely asked to leave.

How does UNION work?

When you use UNION, it performs the following steps:

1. Executes multiple SELECT statements
2. Combines the results
3. Removes duplicate rows
4. Returns the final, unique set of rows

UNION Syntax

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;

UNION Example

Let's say we have two tables: fruits and vegetables. We want to create a list of all produce items.

-- Create and populate the fruits table
CREATE TABLE fruits (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    color VARCHAR(20)
);

INSERT INTO fruits (id, name, color) VALUES
(1, 'Apple', 'Red'),
(2, 'Banana', 'Yellow'),
(3, 'Orange', 'Orange'),
(4, 'Grape', 'Purple');

-- Create and populate the vegetables table
CREATE TABLE vegetables (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    color VARCHAR(20)
);

INSERT INTO vegetables (id, name, color) VALUES
(1, 'Carrot', 'Orange'),
(2, 'Broccoli', 'Green'),
(3, 'Tomato', 'Red'),
(4, 'Eggplant', 'Purple');

-- Now, let's use UNION to combine fruits and vegetables
SELECT name, color FROM fruits
UNION
SELECT name, color FROM vegetables;

This query will return:

name	color
Apple	Red
Banana	Yellow
Orange	Orange
Grape	Purple
Carrot	Orange
Broccoli	Green
Tomato	Red
Eggplant	Purple

Notice how we have 8 rows instead of 10? That's because UNION removed the duplicate 'Orange' and 'Purple' entries!

MySQL UNION ALL Operator

What is UNION ALL?

UNION ALL is like UNION's more relaxed cousin. It combines the results of multiple SELECT statements, but it doesn't bother removing duplicates. It's like throwing a party where everyone's invited, and no one gets turned away at the door!

How does UNION ALL work?

UNION ALL follows these steps:

1. Executes multiple SELECT statements
2. Combines all results, including duplicates
3. Returns the final set of rows (which may include duplicates)

UNION ALL Syntax

SELECT column1, column2, ... FROM table1
UNION ALL
SELECT column1, column2, ... FROM table2;

UNION ALL Example

Let's use our fruits and vegetables tables again, but this time with UNION ALL:

SELECT name, color FROM fruits
UNION ALL
SELECT name, color FROM vegetables;

This query will return:

name	color
Apple	Red
Banana	Yellow
Orange	Orange
Grape	Purple
Carrot	Orange
Broccoli	Green
Tomato	Red
Eggplant	Purple

Now we have all 10 rows, including duplicate colors!

MySQL UNION VS MySQL UNION ALL

Now that we've seen both UNION and UNION ALL in action, let's compare them side by side:

Feature	UNION	UNION ALL
Duplicate Removal	Yes	No
Performance	Slower (due to deduplication)	Faster
Memory Usage	Higher	Lower
Use Case	When unique results are needed	When all results, including duplicates, are needed

When to use UNION

Use UNION when you need to combine results from multiple queries and want to ensure there are no duplicates. It's great for situations like:

1. Creating a unique list of customers from different databases
2. Combining search results from multiple tables
3. Generating reports that require distinct values

-- Example: Find all unique colors of fruits and vegetables
SELECT color FROM fruits
UNION
SELECT color FROM vegetables;

When to use UNION ALL

Use UNION ALL when you want to combine all results, including duplicates, or when you know there won't be any duplicates and want better performance. It's ideal for:

1. Combining log data from multiple sources
2. Aggregating data where duplicates are important
3. Merging data from non-overlapping datasets

-- Example: Count all produce items by color, including duplicates
SELECT color, COUNT(*) as total FROM (
    SELECT color FROM fruits
    UNION ALL
    SELECT color FROM vegetables
) AS all_produce
GROUP BY color;

Conclusion

And there you have it, my dear students! We've journeyed through the land of UNION and UNION ALL, exploring their similarities and differences. Remember, UNION is like a careful party host who ensures everyone is unique, while UNION ALL is the "the more, the merrier" type who welcomes all guests.

As you continue your MySQL adventure, you'll find countless situations where these operators come in handy. Just remember: when you need unique results, go for UNION. When you want it all and want it fast, UNION ALL is your friend.

Now, it's your turn to practice! Try combining different tables in your database using both UNION and UNION ALL. Experiment, make mistakes, and learn from them. That's the best way to truly master these concepts.

Until next time, happy querying, and may your UNIONs always be successful!

Credits: Image by storyset