SQLite 连接：自然左外连接、内连接、交叉连接（带表格示例）

由： 马库斯·艾伦 马库斯·艾伦
Hours 更新

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

简介 SQLite JOIN 子句

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

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

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

SQLite JOIN 子句语法
SQLite JOIN 子句语法

每个连接子句包含：

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

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

步骤1） 在此步骤中，

  1. 打开“我的电脑”并导航到以下目录“目录:\sqlite“和
  2. 然后打开“sqlite3.exe“：

SQLite JOIN 子句

步骤2） 打开数据库“教程SampleDB.db”通过以下命令：

SQLite JOIN 子句

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

SQLite INNER JOIN

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

SQLite INNER JOIN
SQLite INNER JOIN

例如：

在以下示例中，我们将连接两个表“学生“和”部门”与 DepartmentId 一起获取每个学生的部门名称，如下所示：

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

代码解释

INNER JOIN 的工作原理如下：

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

输出

SQLite INNER JOIN 示例

  • INNER JOIN 从学生表和院系表中生成符合条件的记录，“S学生.部门编号 = 部门.部门编号 “。不匹配的行将被忽略并且不会包含在结果中。
  • 这就是为什么这个查询只返回了 8 名来自 IT、数学和物理系的学生中的 10 名。而学生“Jena”和“George”没有被包括在内，因为他们的部门 ID 为空，与部门表中的部门 ID 列不匹配。如下所示：

    SQLite INNER JOIN 示例

相关文章

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 一起获取每个学生的部门名称，如下所示：

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

说明

  • 与前面的例子不同，我们没有写“ON 学生.部门编号 = 部门.部门编号“。我们刚刚写道“使用（部门编号）“。
  • SQLite 自动推断连接条件并比较学生表和部门表的 DepartmentId。
  • 只要您要比较的两列具有相同的名称，您就可以使用此语法。

输出

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

SQLite JOIN 示例

SQLite 自然连接

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

INNER JOIN 和 NATURAL JOIN 之间的区别：

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

例如：

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

说明

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

输出

SQLite 自然连接示例

  • Natural JOIN 将为您提供与我们从 INNER JOIN 和 JOIN USING 示例中获得的输出完全相同的输出。因为在我们的示例中，所有三个查询都是等效的。但在某些情况下，输出将不同于内连接和自然连接。例如，如果有更多具有相同名称的表，则自然连接将匹配所有列。但是，内连接将仅匹配连接条件中的列（更多详细信息请参阅下一节；内连接和自然连接之间的区别）。

SQLite 左外连接

SQL 标准定义了三种类型的 OUTER JOIN：LEFT、RIGHT 和 FULL，但 SQLite 仅支持自然的 LEFT OUTER JOIN。

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

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

因此，您将获得与左连接中的行数相等的行数。这样，​​您将获得来自两个表的匹配行（如 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 相同。

输出

SQLite LEFT OUTER JOIN 示例

  • 如您所见，学生表中的所有行都包括在内，总共有 10 名学生。即使第四名和最后一名学生 Jena 和 George 的部门 ID 不存在于 Departments 表中，他们也包括在内。
  • 在这些情况下，Jena 和 George 的 DepartmentName 值都将为“null”，因为 Departments 表没有与其 DepartmentId 值匹配的 DepartmentName。

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

SQLite 左外连接

SQLite 左外连接

LEFT JOIN 会给出学生表中的所有学生姓名，即使学生的部门 ID 不存在于部门表中。因此，查询不会像 INNER JOIN 那样只给出匹配的行，而是会给出额外的部分，其中包含左表（即学生表）中不匹配的行。

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

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 组合在一起。

输出

SQLite CROSS JOIN 示例

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

  • 部门表中四个部门的四个值与第一位学生 Michel 匹配。
  • 部门表中的四个部门的四个值与第二名学生约翰相匹配。
  • 部门表中的四个部门的四个值与第三个学生杰克相匹配......等等。

总结

运用 SQLite JOIN 查询，您可以将一个或多个表或子查询链接在一起，以从两个表或子查询中选择列。

你可能会喜欢：