解决MySQL查询重复数据问题：理解与应用SQL JOIN

1. 理解查询中的重复数据源头：笛卡尔积

在MySQL（或其他关系型数据库）中，当我们在FROM子句中列出多个表，但未在WHERE子句或ON子句中指定明确的连接条件时，数据库会执行一个“笛卡尔积”（Cartesian Product）。这意味着第一个表中的每一行都会与第二个表中的每一行进行组合，生成一个包含所有可能组合的结果集。

例如，如果booking表有3行数据，student表有3行数据，那么没有连接条件的查询将返回 3 * 3 = 9 行结果。这通常会导致大量重复且不正确的数据，因为许多组合在逻辑上是无效的。原始查询中出现的重复列表和错误的学生姓名正是由于这种隐式的笛卡尔积导致的。

原始问题查询示例：

Select Booking_date,
       Booking_start,
       case WHEN booking.StudentID is NULL THEN NULL ELSE student.First_name end as First_name,
       case WHEN booking.StudentID is NULL THEN NULL ELSE student.Last_name end as Last_name,
       BookingID
from booking, student
where (booking.staffid = '$userid') ORDER BY booking_start ASC

上述查询中，from booking, student 语句指示数据库从两个表中获取数据，但where (booking.staffid = '$userid') 仅过滤了booking表的数据，并未建立booking表与student表之间的关联。因此，booking表中符合staffid条件的每一行都会与student表中的所有行进行组合，产生不正确的重复结果。

2. 解决重复数据：使用显式 JOIN 语句

为了正确地连接表并获取所需的数据，我们需要使用显式的JOIN语句来指定表之间的关联条件。根据业务需求，最常用的两种连接类型是INNER JOIN和LEFT JOIN。

2.1 INNER JOIN：仅返回匹配的记录

INNER JOIN（内连接）用于合并两个或多个表的行，只返回那些在所有连接表中有匹配关系的行。如果某个表中的行在另一个连接表中没有匹配项，则该行不会出现在结果集中。

适用场景： 当你只关心那些在两个表中都有对应记录的数据时。在我们的例子中，如果你只希望看到有明确学生ID且该学生ID在student表中存在的预订记录，就应该使用INNER JOIN。

INNER JOIN 示例：

SELECT
    b.booking_date,
    b.booking_start,
    CONCAT_WS(' ', s.firstname, s.lastname) AS studentname,
    b.bookingid
FROM
    booking b
INNER JOIN
    student s ON b.studentid = s.studentid
WHERE
    b.staffid = '$userid'
ORDER BY
    b.booking_start ASC;

说明：

• booking b 和 student s 为表设置了别名，以简化查询。
• INNER JOIN student s ON b.studentid = s.studentid：明确指定了booking表和student表通过studentid字段进行匹配。只有当booking.studentid在student表中找到对应的studentid时，才会将这两行的信息组合到结果中。
• CONCAT_WS(' ', s.firstname, s.lastname) AS studentname：将学生的姓和名连接成一个完整的学生姓名，并将其命名为studentname，这与期望的输出格式一致。

注意事项： 如果booking表中存在studentid为NULL的记录，或者studentid在student表中没有匹配项，那么这些预订记录将不会出现在INNER JOIN的结果中。

AI建筑知识问答

用人工智能ChatGPT帮你解答所有建筑问题

22

查看详情

2.2 LEFT JOIN：返回左表所有记录及右表匹配记录

LEFT JOIN（左连接），也称为LEFT OUTER JOIN，会返回左表（FROM子句中第一个指定的表）中的所有行，以及右表（LEFT JOIN关键字后指定的表）中与左表匹配的行。如果左表中的某行在右表中没有匹配项，那么右表对应的列将显示为NULL。

适用场景： 当你需要获取左表的所有记录，并且希望在有匹配的情况下显示右表的数据，没有匹配时则显示NULL。在我们的例子中，如果即使预订没有关联的学生（studentid为NULL或在student表中不存在），你仍然希望显示该预订记录，就应该使用LEFT JOIN。

LEFT JOIN 示例：

SELECT
    b.booking_date,
    b.booking_start,
    CONCAT_WS(' ', s.firstname, s.lastname) AS studentname,
    b.bookingid
FROM
    booking b
LEFT JOIN
    student s ON b.studentid = s.studentid
WHERE
    b.staffid = '$userid'
ORDER BY
    b.booking_start ASC;

说明：

• 此查询会返回booking表中所有符合staffid条件的记录。
• 对于booking.studentid在student表中找到匹配的记录，studentname会显示对应的学生姓名。
• 对于booking.studentid为NULL的记录，或者booking.studentid在student表中没有匹配的记录，studentname将显示为NULL。这完美符合了期望输出中2021-10-10|12.00pm|NULL的情况。

3. 现代 JOIN 语法与旧式 JOIN 的对比

强烈建议使用显式的JOIN语法（INNER JOIN ... ON, LEFT JOIN ... ON），而不是将连接条件放在WHERE子句中的旧式语法（FROM table1, table2 WHERE table1.id = table2.id）。

旧式语法示例：

SELECT ...
FROM booking, student
WHERE booking.studentid = student.studentid AND booking.staffid = '$userid';

现代 JOIN 语法优势：

1. 可读性更强： JOIN子句清晰地将连接逻辑与过滤逻辑（WHERE子句）分开，使查询意图一目了然。
2. 更安全： 降低了意外创建笛卡尔积的风险。如果忘记在WHERE子句中添加连接条件，旧式语法很容易导致全表连接。
3. 更灵活： 能够更容易地实现更复杂的连接类型，如RIGHT JOIN、FULL JOIN（在某些数据库中）和链式连接。

4. 总结与最佳实践

• 避免隐式笛卡尔积： 在查询多个表时，务必使用显式的JOIN语句来指定表之间的连接条件，以避免产生重复且不正确的数据。
• 选择正确的 JOIN 类型：
  • 使用INNER JOIN当你只关心在所有连接表中都有匹配项的记录时。
  • 使用LEFT JOIN当你需要保留左表的所有记录，并从右表获取匹配数据（无匹配时显示NULL）时。
  • 根据具体业务需求，还可能需要使用RIGHT JOIN或FULL JOIN。
• 始终使用现代 JOIN 语法： 优先选择INNER JOIN ... ON、LEFT JOIN ... ON等显式连接语法，以提高查询的可读性、安全性和维护性。
• 合理使用别名： 为表设置简短的别名（如booking b, student s）可以使查询语句更简洁易读。
• 考虑性能： 确保连接字段上存在索引，这能显著提高JOIN操作的查询性能。

通过遵循这些原则，您将能够编写出高效、准确且易于维护的MySQL查询语句，有效避免因表连接不当引起的重复数据问题。

以上就是解决MySQL查询重复数据问题：理解与应用SQL JOIN的详细内容，更多请关注php中文网其它相关文章！