SQLite - JOINS（连接）

现代 SQLite：掌握 JOIN 操作

JOIN 子句是 SQL 中一个基本概念，用于根据两个或多个表之间的相关列来组合行。JOIN 允许您从规范化（normalized）的表中检索整合（consolidated）的数据集，这是关系型数据库设计的基石。

现代 SQL 定义了几种类型的连接（JOIN），SQLite 的最新版本都支持这些类型：

• INNER JOIN：返回两个表中具有匹配值的记录。
• LEFT OUTER JOIN（或 LEFT JOIN）：返回左表中的所有记录，以及右表中匹配的记录。如果右侧没有匹配项，则结果为 NULL。
• CROSS JOIN：返回两个表的笛卡尔积，即第一个表中的每一行与第二个表中的每一行组合。
• [New] RIGHT OUTER JOIN & FULL OUTER JOIN：SQLite 3.39.0+ 版本支持，它们提供了更灵活的方式来组合数据集，类似于 PostgreSQL 或 SQL Server 等其他数据库系统。

设置：示例表

在我们的示例中，我们来创建两个表：employees（员工）和 departments（部门）。这个常见的场景有助于说明 JOIN 操作如何在实际环境中工作。

-- 'employees' 表存储员工信息。
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER NOT NULL,
    salary REAL
);

-- 'departments' 表将员工与其部门关联起来。
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    employee_id INTEGER,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

-- 使用示例数据填充表。
INSERT INTO employees (id, name, age, salary)
VALUES
    (1, 'Paul', 32, 70000.00),
    (2, 'Allen', 25, 55000.00),
    (3, 'Teddy', 23, 50000.00),
    (4, 'Mark', 25, 90000.00),
    (7, 'James', 24, 45000.00);

INSERT INTO departments (id, name, employee_id)
VALUES
    (1, 'IT', 1),
    (2, 'Engineering', 2),
    (3, 'Finance', 7),
    (4, 'Marketing', NULL); -- 一个未分配员工的部门

INNER JOIN：查找匹配对

INNER JOIN 是最常见的连接（JOIN）类型。它选择在两个表中具有匹配值的记录。可以将其视为查找两个集合的交集。

让我们找出所有已分配部门的员工。

-- 最佳实践：使用表别名（例如，'e' 代表 employees）以提高可读性。
SELECT
    e.name AS employee_name,
    d.name AS department_name
FROM
    employees AS e
INNER JOIN
    departments AS d ON e.id = d.employee_id;

此查询将产生以下结果：

employee_name  department_name
-------------  ---------------
Paul           IT
Allen          Engineering
James          Finance

LEFT OUTER JOIN：保留左表所有数据

LEFT JOIN 返回左表（employees）中的所有记录，以及右表（departments）中匹配的记录。如果没有匹配项，则右表中的列将为 NULL。这对于查找未分配部门的员工非常有用。

-- 让我们找出所有员工及其部门，包括那些没有部门的员工。
SELECT
    e.name AS employee_name,
    d.name AS department_name
FROM
    employees AS e
LEFT JOIN
    departments AS d ON e.id = d.employee_id;

结果现在包括了没有分配部门的员工 Teddy 和 Mark：

employee_name  department_name
-------------  ---------------
Paul           IT
Allen          Engineering
Teddy          (NULL)
Mark           (NULL)
James          Finance

[更新] RIGHT 和 FULL OUTER JOIN

自 SQLite 3.39.0 版本起，RIGHT JOIN 和 FULL OUTER JOIN 已获得全面支持，使 SQLite 与其他主流 SQL 数据库保持一致。这在旧版本中是一个显著的限制。

RIGHT JOIN：与 LEFT JOIN 相反。它返回右表（departments）中的所有记录，以及左表中匹配的记录。这可以显示哪些部门没有员工。

-- 需要 SQLite 3.39.0+ 版本
SELECT
    e.name AS employee_name,
    d.name AS department_name
FROM
    employees AS e
RIGHT JOIN
    departments AS d ON e.id = d.employee_id;

结果包含了未分配员工的“Marketing”（市场）部门：

employee_name  department_name
-------------  ---------------
Paul           IT
Allen          Engineering
James          Finance
(NULL)         Marketing

FULL OUTER JOIN：结合了 LEFT JOIN 和 RIGHT JOIN 的结果。当左表或右表中存在匹配项时，它会返回所有记录。这对于一次性查看两个表的所有数据非常有用。

-- 需要 SQLite 3.39.0+ 版本
SELECT
    e.name AS employee_name,
    d.name AS department_name
FROM
    employees AS e
FULL OUTER JOIN
    departments AS d ON e.id = d.employee_id;

此结果包括未分配的员工和空部门：

employee_name  department_name
-------------  ---------------
Paul           IT
Allen          Engineering
Teddy          (NULL)
Mark           (NULL)
James          Finance
(NULL)         Marketing

CROSS JOIN：笛卡尔积

CROSS JOIN 会将第一个表中的每一行与第二个表中的每一行进行组合。它在实践中很少使用，并且可能产生庞大的结果集。请谨慎使用！

-- 这会将每个员工与每个部门进行匹配，无论实际的关联如何。
SELECT
    e.name AS employee_name,
    d.name AS department_name
FROM
    employees AS e
CROSS JOIN
    departments AS d;

这将产生 5 名员工 * 4 个部门 = 20 行。

性能与最佳实践

• 为连接键创建索引：为 JOIN 条件中使用的列（例如 departments.employee_id）创建索引，以加快查找速度。SQLite 会自动为 PRIMARY KEY 和 UNIQUE 列创建索引。
• 使用表别名：使用表别名（Table Aliases）可以使您的查询更短、更具可读性，尤其是在复杂的连接中。
• 选择正确的连接类型：理解您的数据和问题。您只需要匹配的数据（INNER JOIN），还是需要主表中的所有数据（LEFT JOIN）？
• 尽早过滤：尽早使用 WHERE 子句来过滤数据，以减少被连接数据集的大小。