JOIN vs. UNION: How Do These Database Operations Differ?

In-depth understanding of database operations: the difference between JOIN and UNION

In database queries, JOIN and UNION are two basic table connection operations. While both involve combining rows from multiple tables, their methods and results are quite different.

The difference between JOIN and UNION

JOIN:

• Performs a Cartesian product (cross join) of all rows of the specified table.
• Filter the resulting dataset based on specified conditions (join conditions).
• Create new rows by combining columns from matching or related rows.

UNION:

• Join rows in the specified table, retaining duplicates.
• Does not create new rows or manipulate data.
• The output contains all rows from each table, arranged vertically.

Example:

The following example illustrates the difference between JOIN and UNION:

<code>表 A:
+----+
| id | name |
+----+
| 1  | John |
| 2  | Mary |
+----+

表 B:
+----+
| id | city |
+----+
| 1  | London |
| 2  | Paris |
+----+</code>

JOIN:

<code>SELECT *
FROM 表A
JOIN 表B ON 表A.id = 表B.id;</code>

Result:

<code>+----+------+----+--------+
| id | name | id | city    |
+----+------+----+--------+
| 1  | John | 1  | London  |
| 2  | Mary | 2  | Paris   |
+----+------+----+--------+</code>

UNION:

<code>SELECT *
FROM 表A
UNION
SELECT *
FROM 表B;</code>

Result:

<code>+----+------+
| id | name |
+----+------+
| 1  | John |
| 2  | Mary |
| 1  | London |
| 2  | Paris |
+----+------+</code>

Obviously, JOIN produces a smaller, more granular data set containing only matching rows, while UNION combines all rows, including duplicates.

The above is the detailed content of JOIN vs. UNION: How Do These Database Operations Differ?. For more information, please follow other related articles on the PHP Chinese website!