sql-using-joins

SQL - 精通联接 (JOIN)

在设计良好的关系型数据库中，数据经过范式化处理并存储在多个表中，以减少冗余。JOIN 子句是一个强大的工具，它允许你根据表之间相关的列，重新组合这些数据，将两个或更多表中的行合并在一起。精通 JOIN 是任何 SQL 用户必备的基本技能。

核心概念：联接谓词 (Join Predicate)

联接通过定义一个“联接谓词”来工作，通常在 ON 子句中指定。这个谓词告诉数据库表之间是如何关联的。最常见的情况是，你将一个表中的主键 (PRIMARY KEY) 与另一个表中的外键 (FOREIGN KEY) 进行联接。

SELECT ...
FROM table1
[JOIN_TYPE] JOIN table2
  ON table1.related_column = table2.related_column;

初学者常犯的一个错误是忘记 ON 子句，这可能导致 CROSS JOIN（笛卡尔积），即第一个表中的每一行都与第二个表中的每一行配对。这会产生一个庞大且不正确的结果集，并严重影响性能。

设置：客户表和订单表 (Customers and Orders)

让我们使用一个经典的电子商务场景。我们有一个 Customers（客户）表和一个 Orders（订单）表。Orders 表有一个 CustomerID 列，它作为外键 (foreign key)，引用了 Customers 表中的 ID。

CREATE TABLE Customers (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    JoinDate DATE NOT NULL
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate TIMESTAMP NOT NULL,
    CustomerID INT,
    Amount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
);

INSERT INTO Customers (ID, Name, JoinDate) VALUES
(1, 'Alice Johnson', '2022-01-15'),
(2, 'Bob Williams', '2022-03-10'),
(3, 'Charlie Brown', '2023-05-20'); -- 一个目前还没有订单的客户

INSERT INTO Orders (OrderID, OrderDate, CustomerID, Amount) VALUES
(101, '2023-11-10 09:30:00', 1, 49.99),
(102, '2023-11-15 14:00:00', 2, 199.50),
(103, '2023-11-16 11:45:00', 1, 25.00);

SQL 联接的类型 (Types of SQL Joins)

不同的联接类型允许你控制最终结果中包含哪些行。将它们想象成维恩图 (Venn diagrams) 会很有帮助。

1. 内联接 (INNER JOIN)

这是最常见的联接类型。它只返回在两个表中都满足联接条件的行。可以把它想象成两个表的交集。

-- 获取客户及其订单金额的列表
SELECT c.Name, o.OrderDate, o.Amount
FROM Customers AS c
INNER JOIN Orders AS o
  ON c.ID = o.CustomerID;

这只会显示 Alice 和 Bob，因为 Charlie 还没有订单。

姓名	订单日期	金额
Alice Johnson	2023-11-10 09:30:00	49.99
Bob Williams	2023-11-15 14:00:00	199.50
Alice Johnson	2023-11-16 11:45:00	25.00

2. 左联接 (LEFT JOIN 或 LEFT OUTER JOIN)

返回左表（先提到的表）中的所有行，以及右表中匹配的行。如果没有匹配项，右表中的列将为 NULL。

-- 获取所有客户及其可能有的订单
SELECT c.Name, o.OrderID, o.Amount
FROM Customers AS c
LEFT JOIN Orders AS o
  ON c.ID = o.CustomerID;

这对于查找没有匹配项的实体非常有用。在这里，我们可以看到 Charlie Brown 的订单详情为 NULL，表明他还没有下订单。

姓名	订单ID	金额
Alice Johnson	101	49.99
Alice Johnson	103	25.00
Bob Williams	102	199.50
Charlie Brown	NULL	NULL

3. 右联接 (RIGHT JOIN 或 RIGHT OUTER JOIN)

与 LEFT JOIN 相反。它返回右表中的所有行，以及左表中匹配的行。如果没有匹配项，左表中的列将为 NULL。这比 LEFT JOIN 不常用，因为你通常可以改写查询来使用 LEFT JOIN，许多开发人员认为那样更直观。

4. 全外联接 (FULL OUTER JOIN)

当左表或右表中有匹配项时，返回所有行。它实际上是 LEFT JOIN 和 RIGHT JOIN 的组合。如果一个表中的行在另一个表中没有匹配项，其对应的列将为 NULL。

5. 自联接 (SELF JOIN)

这并不是一种不同的联接类型，而是一种将表与自身联接的技术。这对于查询层次结构数据非常有用，例如在同一个 Employees（员工）表中查找员工的经理。

-- 示例：一个员工表，其中 ManagerID 引用了另一个员工的 ID
SELECT e.Name AS EmployeeName, m.Name AS ManagerName
FROM Employees AS e
LEFT JOIN Employees AS m
  ON e.ManagerID = m.EmployeeID;

性能与最佳实践

• 索引键： 为了使联接快速，ON 子句中使用的列（特别是外键）应该被索引。没有索引，数据库将不得不进行全表扫描 (full table scan)，这在大型表上会非常慢。
• 明确指定： 只 SELECT 你实际需要的列。SELECT * 对于探索很方便，但在生产代码中效率低下，因为它会传输不必要的数据。
• 尽早筛选： 尽可能早地使用 WHERE 子句筛选数据。这会减少联接操作需要处理的行数。
• 理解你的 ORM： 如果你正在使用对象关系映射器 (Object-Relational Mapper)（例如 SQLAlchemy、TypeORM 或 GORM），理解它生成的联接仍然至关重要。效率低下的 ORM 查询可能是主要的性能瓶颈。使用 ORM 的日志功能来查看实际执行的 SQL。