理解重复数据问题：笛卡尔积的陷阱
在进行多表查询时，如果未能正确指定表之间的关联条件，mysql 数据库可能会返回超出预期的重复数据。这种现象通常是由于产生了“笛卡尔积”（cartesian product）。当你在 from 子句中列出多个表而没有提供明确的 join 条件时，数据库会将第一个表中的每一行与第二个表中的每一行进行组合，导致结果集的行数等于两个表行数的乘积。
考虑以下两个表结构：
Booking 表 | bookingid | booking_date | booking_start | staffid | studentid | status | | :-------- | :----------- | :------------- | :------ | :-------- | :-------- | | 1 | 2021-10-10 | 7.30pm | 1 | 12345678 | ended | | 2 | 2021-10-10 | 11.30am | 1 | 12345679 | ended | | 3 | 2021-10-10 | 12.00pm | 1 | NULL | cancelled |
Student 表 | studentid | firstname | lastname | | :--------- | :-------- | :------- | | 12345678 | john | doe | | 12345679 | mary | doe | | 12345670 | vincent | doe |
如果使用以下查询语句：
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.StudentID = student.StudentID）。尽管 WHERE 子句过滤了 staffid，但 booking 表中的每一行仍会与 student 表中的每一行进行匹配。假设 booking 表有 3 行，student 表有 3 行，那么在 staffid 过滤之前，会产生 3 * 3 = 9 行的笛卡尔积。这会导致每条预订记录都与所有学生记录组合，从而产生大量重复且错误的数据，例如一个预订时间对应多个学生姓名。
解决方案一：使用 INNER JOIN 精确匹配数据
为了避免笛卡尔积并获取准确的关联数据，我们应该使用 JOIN 语句明确指定表之间的连接条件。INNER JOIN（内连接）是最常用的连接类型，它只返回两个表中那些在连接条件上匹配的行。如果某个表中的行在另一个表中没有匹配项，那么这些行将不会出现在结果集中。
要修正上述查询，我们可以使用 INNER JOIN 将 booking 表和 student 表通过 StudentID 字段进行关联：
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 表的 studentid 与 student 表的 studentid 相匹配时，才将这两行的信息组合。
- CONCAT_WS(' ', s.firstname, s.lastname) 用于将学生的姓和名合并为一个 studentname 字段。CONCAT_WS 会忽略 NULL 值，但如果 s.firstname 或 s.lastname 任何一个为 NULL，它会尝试连接非 NULL 的部分。
使用 INNER JOIN 后，查询结果将只包含那些在 booking 表和 student 表中都有匹配 StudentID 的记录。例如，如果 booking 表中有一条 StudentID 为 NULL 的记录，这条记录将不会出现在 INNER JOIN 的结果中，因为它无法与 student 表中的任何记录匹配。
解决方案二：使用 LEFT JOIN 包含所有主表数据
在某些情况下，你可能希望即使主表（通常是 FROM 子句中第一个列出的表）的记录在关联表中没有匹配项，也能将这些记录包含在结果集中。例如，如果一个预订没有关联的学生（booking.StudentID 为 NULL），但你仍然希望显示该预订信息，只是学生姓名显示为 NULL。这时，LEFT JOIN（左连接）就派上用场了。
LEFT JOIN 会返回左表（FROM 子句中第一个表）中的所有行，以及右表中与左表匹配的行。如果左表中的某行在右表中没有匹配项，那么右表的所有列将显示为 NULL。
为了满足显示所有预订记录（包括那些没有关联学生的预订）的需求，我们可以使用 LEFT JOIN：
SELECT
b.booking_date,
b.booking_start,
CASE
WHEN s.studentid IS NOT NULL THEN CONCAT_WS(' ', s.firstname, s.lastname)
ELSE NULL
END 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;在这个 LEFT JOIN 查询中：
- booking b 是左表，student s 是右表。
- 所有 booking 记录都将被返回。
- 如果 b.studentid 在 student 表中找到匹配项，则 s.firstname 和 s.lastname 将填充相应的值。
- 如果 b.studentid 在 student 表中没有匹配项（包括 b.studentid 本身就是 NULL 的情况），那么 s.firstname 和 s.lastname 将为 NULL。
- CASE WHEN s.studentid IS NOT NULL THEN CONCAT_WS(' ', s.firstname, s.lastname) ELSE NULL END AS studentname 确保只有当成功匹配到学生记录时，才生成学生姓名；否则，studentname 列将显示为 NULL，这与我们期望的输出（如 2021-10-10 | 12.00pm | NULL）一致。
现代 JOIN 语法与旧式 WHERE 子句关联
强烈建议使用显式的 JOIN ... ON 语法来关联表，而不是在 FROM 子句中列出多个表并在 WHERE 子句中指定关联条件（即旧式隐式连接）。
旧式隐式连接：
SELECT ... FROM table1, table2 WHERE table1.id = table2.id AND other_conditions;
这种写法容易混淆关联条件与过滤条件，尤其是在查询复杂时，更容易遗漏关联条件，从而意外产生笛卡尔积。
现代显式 JOIN 语法：
SELECT ... FROM table1 INNER JOIN table2 ON table1.id = table2.id WHERE other_conditions;
这种写法将表关联的逻辑与数据过滤的逻辑清晰地分离，提高了 SQL 语句的可读性和可维护性，并有效避免了笛卡尔积的发生。
总结与最佳实践
- 明确关联条件：在进行多表查询时，务必使用 JOIN ... ON 语句明确指定表之间的关联条件，避免产生笛卡尔积。
-
选择合适的 JOIN 类型：
- 使用 INNER JOIN 当你只关心两个表中都有匹配的记录时。
- 使用 LEFT JOIN 当你需要保留左表的所有记录，即使右表没有匹配项时。
- 采用现代 JOIN 语法：优先使用 INNER JOIN、LEFT JOIN 等显式连接语法，而不是在 FROM 子句中列出多个表并在 WHERE 子句中指定连接条件。
- 处理 NULL 值：在 LEFT JOIN 的结果中，右表没有匹配的列将显示为 NULL。在构建最终的显示字段（如 studentname）时，应考虑使用 CASE 语句或 IFNULL 等函数来优雅地处理这些 NULL 值。
- 注意 SQL 注入：示例查询中的 '$userid' 直接拼接字符串到 SQL 中存在 SQL 注入风险。在实际应用中，应使用预处理语句（Prepared Statements）和参数绑定来传递用户输入，确保查询的安全性。
