SQLite Join：Natural Left Outer, Inner, Cross 及其表示例

作者： Marcus Allen Marcus Allen
小时 - 权威教程 更新于

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

SQLite JOIN 子句简介

当您处理包含多个表/数据库时，您经常需要从这些多个表中检索数据。

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

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

SQLite JOIN Clause Syntax
SQLite JOIN 子句语法

每个 join 子句包含：

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

请注意，对于所有以下 SQLite JOIN 表示例，您都需要运行 sqlite3.exe 并打开与示例文档的连接，如下所示：

步骤 1)在此步骤中，

  1. 打开“我的电脑”并导航到目录“C:\sqlite”，然后
  2. 接着打开“sqlite3.exe”

SQLite JOIN Clause

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

SQLite JOIN Clause

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

SQLite INNER JOIN

INNER JOIN 只返回匹配连接条件的行，并消除所有不匹配连接条件的其他行。

SQLite INNER JOIN
SQLite INNER JOIN

示例

在下面的示例中，我们将连接“Students”和“Departments”两个表，使用 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 Example

  • INNER JOIN 产生来自 Students 和 Departments 表中满足条件“Students.DepartmentId = Departments.DepartmentId”的记录。不匹配的行将被忽略，不会包含在结果中。
  • 这就是为什么 10 名学生中有 8 名学生从该查询中返回了 IT、数学和物理系。而“Jena”和“George”学生则未包含在内，因为他们的 department id 为空，不匹配 departments 表中的 departmentId 列。如下所示：

    SQLite INNER JOIN Example

相关文章

SQLite JOIN … USING

可以使用“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)”。

示例

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

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

解释

  • 与前面的示例不同，我们没有写“ON Students.DepartmentId = Departments.DepartmentId”。我们只是写了“USING(DepartmentId)”。
  • SQLite 会自动推断连接条件，并比较 Students 和 Departments 这两个表中的 DepartmentId。
  • 当您要比较的两个列名称相同时，可以使用此语法。

输出

  • 这将为您提供与上一个示例完全相同的结果。

SQLite JOIN Example

SQLite NATURAL JOIN

NATURAL JOIN 类似于 JOIN…USING，不同之处在于它会自动测试两个表中存在的每个列的值之间的相等性。

INNER JOIN 和 NATURAL JOIN 之间的区别

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

示例

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

解释

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

输出

SQLite NATURAL JOIN Example

  • Natural JOIN 将为您提供与 INNER JOIN 和 JOIN USING 示例相同的输出。因为在我们的示例中，所有三个查询都是等效的。但在某些情况下，输出将与 inner join 不同，而不是 natural join。例如，如果存在更多同名的表，那么自然连接将使所有列相互匹配。但是，内部连接只匹配连接条件中的列（更多细节请参见下一节；内部连接和自然连接之间的区别）。

SQLite LEFT OUTER JOIN

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

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

所以，如果左表有 'n' 行，查询的结果将有 'n' 行。但是，对于来自右表的列的值，如果任何值不匹配连接条件，它将包含一个“null”值。

因此，您将获得与左连接的行数相同的行数。这样您将获得两个表中的匹配行（如 INNER JOIN 结果），加上左表中的不匹配行。

示例

在下面的示例中，我们将尝试“LEFT JOIN”来连接“Students”和“Departments”这两个表：

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 Example

  • 正如您所看到的，Students 表中的所有行都包含在内，总共有 10 名学生。即使第四名和最后一名学生 Jena 和 George 的 departmentIds 不存在于 Departments 表中，它们也被包含在内。
  • 在这些情况下，Jena 和 George 的 departmentName 值将是“null”，因为 Departments 表中没有与他们的 departmentId 值匹配的 departmentName。

让我们使用维恩图来更深入地解释之前使用 left join 的查询。

SQLite LEFT OUTER JOIN

SQLite LEFT OUTER JOIN

LEFT JOIN 将提供 Students 表中的所有学生姓名，即使该学生具有不存在于 Departments 表中的 department id。因此，该查询不会仅提供匹配的行，如 INNER JOIN，而是提供不匹配的左表（即 Students 表）的附加部分。

请注意，任何没有匹配部门的学生姓名，其部门名称值将为“null”，因为没有匹配它的值，而这些值是不匹配行中的值。

SQLite CROSS JOIN

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

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

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

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)。

示例

在以下查询中，我们将尝试对 Students 和 Departments 表使用 CROSS JOIN：

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

解释

  • 在 SQLite 的多表选择中，我们只选择了 Students 表中的“studentname”和 Departments 表中的“departmentName”两个列。
  • 对于 cross join，我们没有指定任何连接条件，只是将两个表放在 CROSS JOIN 的中间。

输出

SQLite CROSS JOIN Example

正如您所看到的，结果是 40 行；Students 表中的 10 个值与 Departments 表中的 4 个部门进行了匹配。如下所示：

  • Departments 表中的四个部门的四个值与第一个学生 Michel 匹配。
  • Departments 表中的四个部门的四个值与第二个学生 John 匹配。
  • Departments 表中的四个部门的四个值与第三个学生 Jack 匹配……等等。

摘要

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