【重学 MySQL】二十四、笛卡尔积的错误和正确的多表查询

在数据库查询中，特别是涉及到多表查询时，理解笛卡尔积（Cartesian Product）及其避免方法是非常重要的。笛卡尔积是指在没有明确指定连接条件的情况下，将两个或多个表中的每一行与其他表中的每一行进行组合。这通常会导致结果集急剧增加，且包含大量无用的数据。

笛卡尔积的理解和错误

笛卡尔积的理解

笛卡尔积（Cartesian Product）是数学中的一个重要概念，尤其在集合论和数据库管理中有着广泛的应用。简单来说，笛卡尔积是两个或多个集合中所有元素的所有可能组合。

定义

假设有两个集合A和B，那么A和B的笛卡尔积记作A×B，是一个新的集合，其中包含所有可能的有序对(a, b)，其中a是A中的元素，b是B中的元素。

• 如果A有m个元素，B有n个元素，那么A×B将有m×n个元素。
• 笛卡尔积中的元素是有序的，即(a, b)和(b, a)是不同的，除非A和B是相同的集合且元素可以互换（这通常不是笛卡尔积讨论的重点）。

例子

假设有两个集合：

• A = {1, 2}
• B = {x, y}

那么A和B的笛卡尔积A×B为：

• A×B = {(1, x), (1, y), (2, x), (2, y)}

在数据库中的应用

在数据库查询中，特别是SQL查询中，笛卡尔积通常是不希望出现的结果，因为它会导致查询结果集急剧膨胀，并且包含大量无用的数据。例如，如果有两个表：一个学生表和一个课程表，没有指定连接条件时直接查询这两个表会产生它们的笛卡尔积，即每个学生与每门课程都会组合成一行数据。

为了避免这种情况，SQL提供了多种连接（JOIN）操作，如INNER JOIN、LEFT JOIN、RIGHT JOIN等，允许你指定表之间的关联条件，从而只获取有意义的数据组合。

总结

笛卡尔积是集合论中的一个基础概念，表示两个或多个集合中所有元素的所有可能组合。在数据库查询中，如果不加注意，可能会产生笛卡尔积，导致查询结果集过大且包含大量无用的数据。因此，在编写SQL查询时，应该明确指定连接条件，以避免不必要的笛卡尔积。

笛卡尔积的错误

假设我们有两个表：students（学生表）和courses（课程表）。

• students 表有字段：student_id 和 student_name
• courses 表有字段：course_id 和 course_name

如果我们执行一个 SQL 查询，但没有指定连接条件，如：

SELECT * FROM students, courses;

这将产生一个笛卡尔积，即 students 表中的每一行都会与 courses 表中的每一行进行组合。如果 students 表有 100 行，courses 表有 20 行，那么结果集将包含 2000 行（100 * 20）。这通常不是我们想要的结果，因为它包含了大量无意义的行组合。

正确的多表查询

为了避免笛卡尔积，我们应该使用明确的连接条件来指定表之间的关系。这通常通过 INNER JOIN、LEFT JOIN、RIGHT JOIN 或 FULL JOIN（MySQL 中没有 FULL JOIN，但可以通过其他方式模拟）来实现。

使用 INNER JOIN

假设 students 和 courses 之间通过一个 enrollment 表（选课表）关联，该表有字段 student_id 和 course_id。

SELECT s.student_name, c.course_name
FROM students s
INNER JOIN enrollment e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id;

这个查询将返回所有选课的学生及其所选课程的名称，没有产生笛卡尔积。

使用 WHERE 子句（隐式内连接）

虽然使用 JOIN 语句是更清晰和推荐的方式，但你也可以通过 WHERE 子句来指定连接条件，这实际上执行了一个隐式的内连接。

SELECT s.student_name, c.course_name
FROM students s, courses c, enrollment e
WHERE s.student_id = e.student_id AND e.course_id = c.course_id;

这个查询与上面的 INNER JOIN 示例产生相同的结果，但使用了不同的语法。

总结

• 笛卡尔积是在没有指定连接条件时，将两个或多个表的每一行进行组合的结果，通常会导致大量无用的数据。
• 为了避免笛卡尔积，应该使用明确的连接条件，如 INNER JOIN、LEFT JOIN 等，来指定表之间的关系。
• 虽然可以使用 WHERE 子句来指定连接条件（隐式内连接），但使用 JOIN 语句通常更清晰、更易于维护。
• 建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
• 如果有n个表实现多表的查询，则需要至少n-1个连接条件