MySQL - Union vs Join: Understanding the Difference

Hello, aspiring database enthusiasts! Today, we're going to embark on an exciting journey through the world of MySQL, exploring two powerful tools in our database toolbox: UNION and JOIN. As your friendly neighborhood computer teacher, I'm here to guide you through these concepts with clear explanations and plenty of examples. So, grab your virtual notepads, and let's dive in!

Understanding the Basics

Before we delve into the specifics of UNION and JOIN, let's start with a quick refresher on what these operations do in MySQL.

What is UNION?

UNION is like a master chef combining ingredients from different recipes into one delicious dish. It allows us to combine the result sets of two or more SELECT statements into a single result set.

What is JOIN?

JOIN, on the other hand, is more like a matchmaker. It helps us combine rows from two or more tables based on a related column between them.

Now that we have a basic understanding, let's explore each of these in more detail.

Working of UNION

UNION is used to combine the result set of two or more SELECT statements. Here's the basic syntax:

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

Let's break this down with an example. Imagine we have two tables: fruits and vegetables.

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

INSERT INTO fruits VALUES
(1, 'Apple', 'Red'),
(2, 'Banana', 'Yellow'),
(3, 'Grape', 'Purple');

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

INSERT INTO vegetables VALUES
(1, 'Carrot', 'Orange'),
(2, 'Broccoli', 'Green'),
(3, 'Eggplant', 'Purple');

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

This query will give us a combined list of all fruits and vegetables. The result would look something like this:

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

Note that UNION removes duplicate rows by default. If you want to keep duplicates, you can use UNION ALL instead.

Key Points about UNION:

1. The number and order of columns must be the same in all SELECT statements.
2. The data types of corresponding columns should be compatible.
3. By default, UNION removes duplicate rows. Use UNION ALL to keep duplicates.

Working of JOIN

JOIN is used to combine rows from two or more tables based on a related column between them. There are several types of JOINs, but we'll focus on the most common one: INNER JOIN.

Here's the basic syntax:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Let's illustrate this with an example. Imagine we have two tables: customers and orders.

-- Create and populate the customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    city VARCHAR(50)
);

INSERT INTO customers VALUES
(1, 'John Doe', 'New York'),
(2, 'Jane Smith', 'Los Angeles'),
(3, 'Bob Johnson', 'Chicago');

-- Create and populate the orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product VARCHAR(50),
    amount DECIMAL(10, 2)
);

INSERT INTO orders VALUES
(101, 1, 'Laptop', 999.99),
(102, 2, 'Smartphone', 599.99),
(103, 1, 'Tablet', 299.99);

-- Now, let's use JOIN to combine these tables
SELECT c.customer_name, o.product, o.amount
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;

This query will give us a list of customers and their orders. The result would look something like this:

customer_name	product	amount
John Doe	Laptop	999.99
John Doe	Tablet	299.99
Jane Smith	Smartphone	599.99

Key Points about JOIN:

1. JOIN combines rows from different tables based on a related column between them.
2. The ON clause specifies the condition for joining the tables.
3. There are different types of JOINs (INNER, LEFT, RIGHT, FULL), each with different behaviors.

UNION vs JOIN: When to Use Which?

Now that we've explored both UNION and JOIN, you might be wondering when to use each. Let's break it down:

Use UNION when:

1. You want to combine rows from similar tables or queries.
2. The tables have the same structure (number and data types of columns).
3. You need to eliminate duplicates (or use UNION ALL to keep them).

Use JOIN when:

1. You want to combine columns from different tables.
2. There's a logical relationship between the tables.
3. You need to retrieve data that spans multiple tables.

Here's a handy comparison table:

Feature	UNION	JOIN
Purpose	Combines rows	Combines columns
Table Structure	Must be similar	Can be different
Result	Vertical combination	Horizontal combination
Duplicate Handling	Removes by default (UNION ALL keeps)	Depends on JOIN type
Performance	Generally faster for large datasets	Can be slower for complex JOINs

Remember, choosing between UNION and JOIN depends on your specific data structure and the result you're trying to achieve. Practice with different scenarios to get a feel for when to use each.

In conclusion, both UNION and JOIN are powerful tools in MySQL, each serving different purposes. UNION helps us combine similar data vertically, while JOIN allows us to bring related data together horizontally. As you continue your MySQL journey, you'll find countless situations where these operations will come in handy. Keep practicing, and soon you'll be a MySQL maestro!

Happy querying, future database wizards!

Credits: Image by storyset