Union vs Join - (MySQL Join)

In MySQL, there are two types of operations that allow you to combine data from multiple tables: UNION and JOIN. While they may seem similar at first glance, they each serve a different purpose. In this tutorial, we'll compare UNION and JOIN in MySQL.

Syntax

The syntax for UNION is as follows:

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

The UNION operation combines the results of two or more SELECT statements into a single result set.

The syntax for is as follows:

SELECT column1, column2, ...
FROM table1
JOIN table2
ON table1.column = table2.column

The JOIN operation combines rows from two or more tables based on a related column between them.

Example

To illustrate the difference between UNION and JOIN, let's consider a simple example. We have two tables: Customers and Orders.

Customers table:

+----+-----------+-------+
| id | name      | email |
+----+-----------+-------+
|  1 | John Doe  |       |
|  2 | Jane Doe  |       |
|  3 | Bob Smith |       |
+----+-----------+-------+

Orders table:

+----+------------+-------+
| id | order_date | total |
+----+------------+-------+
|  1 | 2022-01-01 |   100 |
|  2 | 2022-01-02 |    50 |
|  3 | 2022-01-03 |   200 |
+----+------------+-------+

UNION Example

We want to combine the name column from the Customers table with the order_date column from the Orders table. Here's how we can do that using UNION:

SELECT name, NULL AS order_date
FROM Customers
UNION
SELECT NULL AS name, order_date
FROM Orders

The result of the above query would be:

+-----------+------------+
| name      | order_date |
+-----------+------------+
| John Doe  | NULL       |
| Jane Doe  | NULL       |
| Bob Smith | NULL       |
| NULL      | 2022-01-01 |
| NULL      | 2022-01-02 |
| NULL      | 2022-01-03 |
+-----------+------------+

JOIN Example

We want to match up the order data (order_date and total columns) from the Orders table with the appropriate customer based on the id column. Here's how we can do that using a JOIN:

SELECT name, order_date, total
FROM Customers
JOIN Orders
ON Customers.id = Orders.id

The result of the above query would be:

+-----------+------------+-------+
| name      | order_date | total |
+-----------+------------+-------+
| John Doe  | 2022-01-01 |   100 |
| Jane Doe  | 2022-01-02 |    50 |
+-----------+------------+-------+

Output

The output of UNION and JOIN operations depends on the specific query that you construct.

Explanation

UNION is used to combine results from multiple SELECT statements, whereas JOIN is used to combine rows from multiple tables based on a related column between them.

In the example above, our UNION query combined the name column from the Customers table with the order_date column from the Orders table, whereas our JOIN query matched up the order data from the Orders table with the appropriate customer based on the id column.

Use

UNION is useful for combining results from multiple tables that have a similar structure. JOIN is useful for combining rows from multiple tables based on a related column between them.

Important Points

• UNION combines results from multiple SELECT statements, whereas JOIN combines rows from multiple tables based on a related column between them.
• UNION requires both SELECT statements to have the same number of columns with compatible data types.
• JOIN requires a related column between the tables in order to join the rows together.

Summary

In this tutorial, we compared UNION and JOIN operations in MySQL. We discussed their syntax, explained the difference between the two, and provided examples of how to use each. Understanding the difference between UNION and JOIN can help you choose the appropriate operation to combine data from multiple tables in your MySQL queries.