MySQL - Union vs Join

MySQL - UNION vs. JOIN

In relational databases, data is often split across multiple tables. To retrieve a complete picture, you need to combine this data. MySQL provides two primary tools for this: UNION and JOIN. Although both combine data, they do so in fundamentally different ways and for different purposes.

The Fundamental Difference: Widening vs. Lengthening Data

The easiest way to understand the difference is with an analogy:

• JOIN adds columns from other tables. It makes your result set wider by finding related data in other tables and adding it as new columns. Think of it as looking up information in a second spreadsheet and adding it next to the matching rows in your first spreadsheet.
• UNION adds rows from other queries. It makes your result set longer by stacking the results of one query on top of another. Think of it as copying the rows from a second spreadsheet and pasting them at the bottom of your first one.

UNION in Detail: Combining Rows

The UNION operator combines the result-sets of two or more SELECT statements. It has two strict rules:

• Each SELECT statement within the UNION must have the same number of columns.
• The columns must also have similar data types in the same order.

By default, UNION removes duplicate rows from the final result set. If you want to include all rows, including duplicates, use UNION ALL, which is also faster because it doesn’t need to check for duplicates.

Example: Combining Active and Archived Users

Imagine you have two tables, active_users and archived_users, with a similar structure. To get a single list of all user emails, you would use UNION.

-- Table: active_users
-- +----+------------------+
-- | id | email            |
-- +----+------------------+
-- | 1  | alice@example.com|
-- | 2  | bob@example.com  |
-- +----+------------------+

-- Table: archived_users
-- +---------+-------------------+
-- | user_id | user_email        |
-- +---------+-------------------+
-- | 101     | charlie@example.com |
-- | 2       | bob@example.com     |
-- +---------+-------------------+

-- The UNION query:
SELECT email FROM active_users
UNION
SELECT user_email FROM archived_users;

The column names in the SELECT lists can be different (email vs. user_email), but their number (one) and data type (VARCHAR) must be compatible. The final result will have one column, and the duplicate ‘bob@example.com’ will be removed.

email
alice@example.com
bob@example.com
charlie@example.com

JOIN in Detail: Combining Columns

A JOIN clause is used to combine rows from two or more tables based on a related column between them. The most common type is INNER JOIN, which returns records that have matching values in both tables.

Example: Combining Customers and Orders

This is the classic use case. You have a customers table and an orders table. To see which customer placed which order, you join them on their common field (id in customers and customer_id in orders).

-- Table: customers
-- +----+--------+
-- | id | name   |
-- +----+--------+
-- | 1  | Alice  |
-- | 2  | Bob    |
-- +----+--------+

-- Table: orders
-- +----------+-------------+--------------+
-- | order_id | customer_id | product      |
-- +----------+-------------+--------------+
-- | 101      | 2           | Laptop       |
-- | 102      | 1           | Mouse        |
-- | 103      | 2           | Keyboard     |
-- +----------+-------------+--------------+

-- The JOIN query:
SELECT c.name, o.product
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id;

The result combines columns from both tables, creating a wider, more informative data set.

name	product
Bob	Laptop
Alice	Mouse
Bob	Keyboard

Key Differences Summarized

Aspect	UNION	JOIN
Purpose	Combines rows from two or more result sets into a single set.	Combines columns from two or more tables based on a related key.
Data Direction	Appends data vertically (lengthens the result).	Appends data horizontally (widens the result).
Rules	Requires the same number of columns and compatible data types.	Requires a join condition (e.g., ON clause) that defines the relationship.
Duplicate Handling	UNION removes duplicates by default. UNION ALL allows them.	Returns all matching rows based on the join condition; duplicates are not inherently removed.
Resulting Columns	The column names from the first SELECT statement are used.	The result set includes columns from all joined tables.

When to Use Which

• Use UNION when: You want to combine lists of similar things from different tables or queries. For example, getting a list of all contact phone numbers from customers, suppliers, and employees tables.
• Use JOIN when: You want to enrich your data by adding related information from other tables. For example, finding the name of the customer (customers table) who bought a specific product (orders table).