一天学会sqlite--Chapter 7: 连接

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

SQLite JOIN 子句简介

当您在一个包含多个表的数据库中工作时，经常需要从这多个表中获取数据。

通过 JOIN 子句，您可以连接两个或多个表或子查询。此外，您还可以定义需要通过哪一列和哪些条件将表连接起来。

任何 JOIN 子句都必须使用以下语法：

1. 每个连接子句都包含

2. 作为左表的表或子查询；连接子句之前的表或子查询（在其左侧）。

3. 连接操作符 - 指定连接类型（内连接、左外连接或交叉连接）。
4. JOIN-constraint - 在指定要连接的表或子查询后，需要指定连接约束，这将是一个条件，根据连接类型选择符合该条件的匹配记录。

请注意，以下所有示例都必须运行 sqlite3.exe，并按照流程打开与示例数据库的连接：

步骤 1） 在此步骤中、

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

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

现在，您可以在数据库中运行任何类型的查询。

SQLite INNER JOIN

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。

输出：

• INNER JOIN 从学生表和系表中产生符合条件的记录，条件是 “Students.DepartmentId = Departments.DepartmentId”。不匹配的记录将被忽略，不会包含在结果中。
• 这就是为什么这次查询只返回了 10 个学生中的 8 个学生，而且是信息技术、数学和物理系的学生。而学生 “Jena ”和 “George ”没有被包括在内，因为他们的系Id为空，与系表中的departmentId列不匹配。如下所示：

SQLite JOIN … USING

为了避免冗余，INNER JOIN 可以使用 “USING ”子句来书写，所以不用写 “ON Students.DepartmentId = Departments.DepartmentId ”，可以直接写 “USING( DepartmentID )”。

您可以使用 "JOIN . USING"。在这种情况下，无需使用 on 条件重复列名，只需说明列名，SQLite 就会检测到。

INNER JOIN 和 JOIN . 使用：

使用 “JOIN ...USING ”时，不需要写入连接条件，只需写入两个连接表之间的连接列，而不是写入表 1 “INNER JOIN table2 ON table1.cola = table2.cola”，我们可以写成 “table1 JOIN table2 USING(cola)”。

示例

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

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

• 解释

• 与前面的示例不同，我们没有写“ ON Students.DepartmentId = Departments.DepartmentId ”。我们只写了“ USING( DepartmentId ) ”。

• SQLite 会自动推断连接条件，并比较学生表和部门表中的 DepartmentId。
• 只要要比较的两列名称相同，就可以使用这种语法。

输出

• 结果与上例完全相同：

SQLite 自然连接

自然连接类似于...使用连接，不同之处在于它会自动检测两个表中存在的每一列的值是否相等。

**内连接与自然连接的区别

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

示例*

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

解释

• 我们不需要编写包含列名的连接条件（就像在 INNER JOIN 中那样）。我们甚至不需要写一次列名（就像在 JOIN USING 中那样）。
• 自然连接将扫描两个表中的列。它将检测到条件应该是通过比较两个表 “学生 ”和 “部门 ”中的 DepartmentId 来组成的。

输出

• 自然连接的输出结果与 INNER JOIN 和 JOIN USING 示例的输出结果完全相同。因为在我们的示例中，这三个查询是等价的。但在某些情况下，内部连接的输出结果会与自然连接不同。例如，如果有多个表具有相同的名称，那么自然连接将匹配所有列。然而，内部连接将只匹配连接条件中的列（更多详情，请参阅下一节；内部连接与自然连接的区别）。

SQLite LEFT OUTER JOIN

SQL 标准定义了三种外连接类型： 但 SQLite 只支持左外连接。

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

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

因此，你将得到与左连接中的行数相同的行数。这样，你将从两个表中得到匹配的记录（就像内连接结果一样），再加上左表中不匹配的记录。

示例

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

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

解释

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

输出

• 可以看到，学生表中的所有行都包含在内，总共有 10 名学生。即使部门表中不存在第四个和最后一个学生，即杰娜和乔治的部门 ID，他们也会被包含在内。
• 在这种情况下，杰娜和乔治的系名值都将是 “空”，因为系表中没有与他们的系Id值相匹配的系名。

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

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

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

SQLite CROSS JOIN

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）合并为两行。
• 同样，将列 A 的第二个值（2）与列 B 的两个值（3,4）（2,3）、（2,4）合并为两行。

示例

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

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

解释

• 在选择子句中，我们只选择了学生表中的两列 “studentname ”和部门表中的 “departmentName”。
• 对于交叉连接，我们没有指定任何连接条件，只是在两个表中间加上了 CROSS JOIN。

输出：

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

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

......以此类推。

总结

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