SQLite 连接方式：自然左外连接、内连接、十字连接（带表格）。

SQLite 支持不同类型的 SQL 连接，例如 INNER JOIN、LEFT OUTER JOIN 和 CROSS JOIN。正如我们将在本教程中看到的那样，每种类型的 JOIN 都用于不同的情况。

简介 SQLite JOIN 子句

当您处理具有多个表的数据库时，您经常需要从这多个表中获取数据。

使用 JOIN 子句，您可以通过连接两个或多个表或子查询来链接它们。此外，您还可以定义需要通过哪些列以及通过哪些条件链接表。

任何 JOIN 子句都必须具有以下语法：

每个连接子句包含：

• 作为左表的表或子查询；连接子句之前（其左边）的表或子查询。
• JOIN 运算符 — 指定连接类型（INNER JOIN、LEFT OUTER JOIN 或 CROSS JOIN）。
• JOIN 约束 – 在指定要连接的表或子查询后，您需要指定一个连接约束，该约束是一个条件，根据连接类型将选择符合该条件的匹配行。

请注意，对于以下所有 SQLite JOIN 表示例，您必须运行 sqlite3.exe 并打开与示例数据库的连接，如下所示：

步骤1） 在此步骤中，打开“我的电脑”，导航到以下目录“C:\sqlite”，然后打开“sqlite3.exe”：

步骤2） 使用以下命令打开数据库“TutorialsSampleDB.db”：

现在您已准备好在数据库上运行任何类型的查询。

SQLite INNER JOIN

INNER JOIN 只返回符合连接条件的行，并删除所有不符合连接条件的其他行。

例如：

在以下示例中，我们将使用 DepartmentId 连接“Students”和“Departments”两个表，以获取每个学生的院系名称，如下所示：

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

代码解释

INNER JOIN 的工作原理如下：

• 在 Select 子句中，您可以从两个引用表中选择您想要选择的任何列。
• INNER JOIN 子句写在用“From”子句引用的第一个表之后。
• 然后将连接条件指定为 ON。
• 可以为引用表指定别名。
• INNER 词是可选的，您只需写 JOIN 即可。

输出

INNER JOIN 操作会从学生表和院系表中筛选出符合条件“Students.DepartmentId = Departments.DepartmentId”的记录。不匹配的行将被忽略，不会包含在结果中。

这就是为什么从信息技术、数学和物理系的10名学生中，查询结果只返回了8名学生。而学生“Jena”和“George”没有被包含在内，因为他们的系ID为空，与系表中的departmentId列不匹配。具体如下：

SQLite 加入…使用

可以使用“USING”子句来编写 INNER JOIN 以避免冗余，因此，不要编写“ON Students.DepartmentId = Departments.DepartmentId”，而只需编写“USING(DepartmentID)”。

只要您要在连接条件中比较的列名称相同，就可以使用“JOIN .. USING”。在这种情况下，无需使用 on 条件重复它们，只需说明列名称和 SQLite 将会检测到。

INNER JOIN 和 JOIN 的区别……USING：

使用“JOIN … USING”时，您无需编写连接条件，只需编写两个连接表共有的连接列即可。例如，与其编写“table1 “INNER JOIN table2 ON table1.cola = table2.cola”，不如编写“table1 JOIN table2 USING(cola)”。

例如：

在以下示例中，我们将使用 DepartmentId 连接“Students”和“Departments”两个表，以获取每个学生的院系名称，如下所示：

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments USING(DepartmentId);

说明

• 与前面的例子不同，我们没有写“ON Students.DepartmentId = Departments.DepartmentId”，而只是写了“USING(DepartmentId)”。
• SQLite 自动推断连接条件并比较学生表和部门表的 DepartmentId。
• 只要您要比较的两列具有相同的名称，您就可以使用此语法。

输出

这将给出与前面的示例完全相同的结果：

SQLite 自然连接

NATURAL JOIN 与 JOIN...USING 类似，不同之处在于它会自动测试两个表中存在的每一列的值是否相等。

INNER JOIN 和 NATURAL JOIN 之间的区别：

• 在 INNER JOIN 中，您必须指定一个连接条件，INNER JOIN 会使用该条件连接两个表。而在自然连接中，您无需编写连接条件。您只需填写两个表的名称即可，无需任何条件。自然连接会自动检查两个表中每一列的值是否相等。自然连接会自动推断连接条件。
• 在自然连接中，两个表中所有同名的列都会相互匹配。例如，如果我们有两个表，其中有两个共同的列名（两个表中存在同名的两列），那么自然连接将通过比较两个列的值而不是仅比较一列的值来连接这两个表。

例如：

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
Natural JOIN Departments;

说明

• 我们不需要像在 INNER JOIN 中那样编写包含列名的连接条件。我们甚至不需要像在 JOIN USING 中那样编写一次列名。
• 自然连接将扫描两个表中的两列。它将检测到条件应由比较 Students 和 Departments 两个表中的 DepartmentId 组成。

输出

自然连接 (NATURAL JOIN) 的输出结果与内连接 (INNER JOIN) 和使用连接 (JOIN USING) 的示例完全相同，因为在我们的示例中，这三个查询是等效的。但在某些情况下，自然连接的输出结果会与内连接不同。例如，如果存在多个同名表，自然连接会将所有列相互匹配。而内连接则只会匹配连接条件中指定的列。

SQLite 左外连接

SQL 标准定义了三种类型的外连接：左连接、右连接和全连接，但是 SQLite 仅支持自然的 LEFT OUTER JOIN。

在 LEFT OUTER JOIN 中，从左表中选择的所有列的值都将包含在查询结果中，因此无论该值是否与连接条件匹配，它都将包含在结果中。

因此，如果左表有 n 行，查询结果也将有 n 行。但是，对于来自右表的列值，如果任何值不符合连接条件，则会包含一个“null”值。

因此，您将获得与左连接中的行数相等的行数。这样，​​您将获得来自两个表的匹配行（如 INNER JOIN 结果），以及来自左表的不匹配行。

例如：

在下面的例子中，我们将尝试使用“LEFT JOIN”连接“学生”和“部门”两个表：

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students             -- this is the left table
LEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

说明

• SQLite LEFT JOIN 语法与 INNER JOIN 相同；在两个表之间写入 LEFT JOIN，然后连接条件位于 ON 子句之后。
• from 子句后的第一个表是左表。而自然 LEFT JOIN 后指定的第二个表是右表。
• OUTER 子句是可选的；LEFT natural OUTER JOIN 与 LEFT JOIN 相同。

输出

如您所见，学生表中的所有行都已包含在内，总共有 10 名学生。即使第四名和最后一名学生 Jena 和 George 的 departmentId 在 Departments 表中不存在，他们也被包含在内。

在这种情况下，Jena 和 George 的 departmentName 值都将为“null”，因为 departments 表中没有与 departmentId 值匹配的 departmentName。

让我们用维恩图对前面使用左连接的查询进行更深入的解释：

LEFT JOIN 会返回 students 表中所有学生的姓名，即使某个学生的部门 ID 在 departments 表中不存在。因此，该查询不会像 INNER JOIN 那样只返回匹配的行，还会返回来自左侧表（即 students 表）的不匹配行。

请注意，任何没有匹配部门的学生姓名在部门名称中都会有一个“空”值，因为没有匹配的值，而这些值是不匹配的行中的值。

SQLite 交叉加入

CROSS JOIN 通过将第一个表中的所有值与第二个表中的所有值进行匹配，得出两个连接表的选定列的笛卡尔积。

因此，对于第一个表中的每个值，您将从第二个表中获得“n”个匹配项，其中 n 是第二个表的行数。

与 INNER JOIN 和 LEFT OUTER JOIN 不同，使用 CROSS JOIN 时，不需要指定连接条件，因为 SQLite CROSS JOIN 不需要它。

此 SQLite 将第一个表中的所有值与第二个表中的所有值结合起来，即可得到一个逻辑结果集。

例如，如果您从第一个表中选择一列（colA），从第二个表中选择另一列（colB）。colA 包含两个值（1，2），colB 也包含两个值（3，4）。

那么 CROSS JOIN 的结果将是四行：

• 通过将 colA 的第一个值 1 与 colB 的两个值 (3,4) 组合为 (1,3)、(1,4) 来得到两行。
• 同样，将 colA 的第二个值 2 与 colB 的两个值 (3,4) (2,3)、(2,4) 组合成两行。

例如：

在以下查询中，我们将尝试在学生表和部门表之间进行 CROSS JOIN：

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
CROSS JOIN Departments;

说明

• 在 SQLite 从多张表中选择，我们只需从学生表中选择两列“studentname”，从部门表中选择“departmentName”。
• 对于交叉连接，我们没有指定任何连接条件，只是将两个表用 CROSS JOIN 连接在一起。

输出

如您所见，结果为 40 行；学生表中的 10 个值与部门表中的 4 个部门匹配。如下所示：

• 部门表中四个部门的四个值与第一位学生 Michel 匹配。
• 部门表中四个部门的四个值与第二个学生约翰相符。
• 部门表中四个部门的四个值与第三名学生杰克相符……依此类推。

常见问题