sql-union-vs-join

SQL：UNION 与 JOIN

在 SQL 中，UNION 和 JOIN 子句都用于组合来自两个或多个表的数据。然而，它们以根本不同的方式实现此目的。理解它们之间的区别对于有效的数据库查询和数据操作至关重要。

理解它们之间差异最简单的方法是：UNION 将一个表的行附加到另一个表（垂直组合），而 JOIN 根据表之间的关联列来组合不同表的列（水平组合）。

UNION 和 UNION ALL 的工作方式

UNION 操作符用于组合两个或多个 SELECT 语句的结果集。它通过将一个查询结果的行附加到另一个查询结果来工作。然而，要使 UNION 起作用，必须满足某些条件：

• 在 UNION 中的每个 SELECT 语句必须具有相同数量的列。
• 列的数据类型也必须相似。
• 每个 SELECT 语句中的列必须按相同顺序排列。

UNION 操作符有两种变体：

• UNION：此操作符组合结果，然后执行去重排序以移除任何重复行。此操作可能消耗大量资源。
• UNION ALL：此操作符组合结果，但包含所有重复行。它比 UNION 快得多，因为它不需要检查重复项。在大多数情况下，如果您确定组合的数据集是唯一的或者允许重复，为了性能，推荐使用 UNION ALL。

语法

SELECT column1, column2 FROM table1
UNION -- or UNION ALL
SELECT column1, column2 FROM table2;

示例：组合活跃用户和已归档用户

假设您有两个结构相同的表：ActiveUsers（活跃用户）和 ArchivedUsers（已归档用户）。我们希望获得所有用户电子邮件的单一列表。

首先，ActiveUsers 表：

CREATE TABLE ActiveUsers (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO ActiveUsers (user_id, user_name, email) VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob', 'bob@example.com');

接下来是 ArchivedUsers 表，其中可能包含一个仍然活跃的用户（仅用于演示）：

CREATE TABLE ArchivedUsers (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO ArchivedUsers (user_id, user_name, email) VALUES
(2, 'Bob', 'bob@example.com'),
(3, 'Charlie', 'charlie@example.com');

现在，让我们使用 UNION 来获取唯一的电子邮件列表：

SELECT email FROM ActiveUsers
UNION
SELECT email FROM ArchivedUsers;

输出 (UNION)

结果将移除重复项。请注意，‘bob@example.com’ 只出现一次。

电子邮件
alice@example.com
bob@example.com
charlie@example.com

使用 UNION ALL 将产生不同的结果：

SELECT email FROM ActiveUsers
UNION ALL
SELECT email FROM ArchivedUsers;

输出 (UNION ALL)

结果包含来自两个查询的所有行。

电子邮件
alice@example.com
bob@example.com
bob@example.com
charlie@example.com

JOIN 的工作方式

JOIN 子句用于根据两个或多个表之间的关联列来组合行。这是一种水平组合，创建一个新的、更宽的结果集，其中包含所有连接表的列。

JOIN 有几种类型：

• INNER JOIN：返回两个表中都有匹配值的记录。这是最常见的连接类型。
• LEFT JOIN（或 LEFT OUTER JOIN）：返回左表中的所有记录，以及右表中的匹配记录。如果没有匹配项，则右侧结果为 NULL。
• RIGHT JOIN（或 RIGHT OUTER JOIN）：返回右表中的所有记录，以及左表中的匹配记录。如果没有匹配项，则左侧结果为 NULL。
• FULL OUTER JOIN：当左右表中存在任何匹配项时，返回所有记录。它结合了 LEFT JOIN 和 RIGHT JOIN 的功能。

语法 (INNER JOIN)

相比于旧的逗号分隔语法，强烈推荐使用现代的显式 JOIN 语法。

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

示例：组合客户和订单

让我们创建 Customers（客户）表和 Orders（订单）表。

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO Customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO Orders VALUES (101, 1, '2023-10-26', 150.00), (102, 2, '2023-10-26', 75.50), (103, 1, '2023-10-27', 25.00);

我们可以连接这些表来查看哪些客户下了哪些订单。我们使用 INNER JOIN，因为我们只希望看到实际下过订单的客户。

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM
    Customers c
INNER JOIN
    Orders o ON c.customer_id = o.customer_id;

输出

结果合并了两个表中的列。请注意，没有订单的 ‘Charlie’ 不会出现在结果中。

客户姓名	订单 ID	金额
Alice	101	150.00
Bob	102	75.50
Alice	103	25.00

UNION 与 JOIN：主要区别

方面	UNION / UNION ALL	JOIN
目的	将多个表的行附加在一起（垂直组合）。	根据关联键组合来自多个表的列（水平组合）。
结果集	列数保持不变；行数增加。	列数增加（表列数之和，除非另有指定）；行数取决于匹配项。
列要求	表必须具有相同数量的列且数据类型兼容。	表必须至少有一个公共的、可连接的列（键）。其他列的数据类型可以不同。
重复项处理	UNION 移除重复行。UNION ALL 包含重复行。	如果连接条件匹配多行，JOIN 操作可能会产生重复值。
语法	置于两个 SELECT 语句之间。	在 FROM 子句中使用 ON 条件来指定关系。
用例	整合结构相同的表中的数据，例如合并 Sales_Q1 和 Sales_Q2。	查询来自不同实体的关联数据，例如检索 Users 及其 Profiles。