msyql多表连接的方式、区别及每一种连接的使用场景

在MySQL中，多表连接是处理关系数据库中数据关联查询的基础。以下是几种主要的多表连接方式，它们的使用场景及区别：

1. MySQL多表连接方式

1. 交叉连接（CROSS JOIN），也称笛卡尔积连接

2. 实例：当需要获取两个表所有记录的任意组合时使用。例如，有一个员工表（Employees）和一个部门表（Departments），想要知道每个员工可能分配到每个部门的情况。

3. 内连接（INNER JOIN）

4. 实例：当需要查询两个表中匹配的数据时使用。例如，查询所有有订单的客户信息，从客户表（Customers）和订单表（Orders）中选取匹配的记录。

5. 左外连接（LEFT JOIN 或 LEFT OUTER JOIN）

6. 实例：当需要显示左表的所有记录，以及右表中与之匹配的记录，如果没有匹配则显示NULL。比如，显示所有员工及其所在的部门，即使某些员工尚未分配到任何部门。

7. 右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）

8. 实例：与左外连接相反，显示右表的所有记录，以及左表中与之匹配的记录，未匹配的左表记录显示为NULL。适用于查询所有部门及部门内的员工，即使某些部门没有员工。

9. 全外连接（FULL JOIN 或 FULL OUTER JOIN）

10. 注意：MySQL原生不支持FULL JOIN，但可以通过UNION来模拟。全外连接用于显示左表和右表中所有记录的组合，两边不匹配的记录用NULL填充。

11. 自然连接（NATURAL JOIN）

12. 实例：自动连接具有相同列名的表，无需显式指定连接条件。例如，两个表都有关联的ID列，可以使用自然连接来快速匹配相同ID的记录。

13. 自连接（SELF JOIN）

14. 实例：表自身与自己进行连接，常用于层次结构或递归查询。例如，查询员工及其直接上级信息，从员工表中自我连接来表示上下级关系。

2. 使用场景比较

• 交叉连接：适合于探索性分析，理解不同数据集之间的组合可能性。
• 内连接：是最常用的连接类型，适用于需要精确匹配两个表中记录的场景。
• 左/右外连接：适用于需要包含一方全部数据，而另一方数据可选的场景，常用于报表和数据分析。
• 全外连接：在需要同时查看两边不完全匹配的所有记录时使用，尽管MySQL不直接支持，但在处理缺失数据时很有用。
• 自然连接：简化查询编写，当表结构设计良好且连接键一致时非常方便。
• 自连接：处理层次或网络结构数据，如组织结构、树状菜单等。

3. 每种连接方式的区别

• 匹配程度：内连接只显示匹配项，外连接还包括不匹配项，全外连接展示所有匹配和不匹配的记录，交叉连接生成所有可能的配对。
• 数据方向：左/右外连接根据哪一侧的数据是必须展示的来决定。
• 数据完整性：外连接可以保证查询结果中包含至少一侧表的所有记录，而内连接只包含匹配记录。
• 自然连接自动基于同名列进行匹配，减少了书写复杂度，但对表设计有一定要求。
• 自连接允许表作为自己的参照，用于处理复杂的查询需求，尤其是涉及层级关系时。

主表(TransVouch)、子表(TransVouchs)、孙表（BoxdetailTransVouch）

1. 主表和子表通过TransVouch.ID和TransVouchs.ID关联
2. 子表和孙表通过TransVouchs.autoID和BoxdetailTransVouch.RdsID连接
3. 主表用于存储单据表头信息，子表用于存储单据物料明细信息，孙表用于存储条码信息，自动补全需要的字段，基于上述连列的连接方式具体实例

1. 内连接 (INNER JOIN)

实例：查询所有有条码信息的单据及其详细信息。

SELECT 
    TV.ID, TV.VouchDate, TVS.MaterID, BDTV.Barcode
FROM 
    TransVouch TV
INNER JOIN 
    TransVouchs TVS ON TV.ID = TVS.ID
INNER JOIN 
    BoxdetailTransVouch BDTV ON TVS.autoID = BDTV.RdsID;

2. 左外连接 (LEFT JOIN)

实例：查询所有单据及其物料信息，即使某些物料没有条码信息。

SELECT 
    TV.ID, TV.VouchDate, TVS.MaterID, BDTV.Barcode
FROM 
    TransVouch TV
LEFT JOIN 
    TransVouchs TVS ON TV.ID = TVS.ID
LEFT JOIN 
    BoxdetailTransVouch BDTV ON TVS.autoID = BDTV.RdsID;

3. 右外连接 (RIGHT JOIN)

实例：查询所有有条码信息的物料及其所属的单据，即使某些单据没有条码信息记录。

SELECT 
    TV.ID, TV.VouchDate, TVS.MaterID, BDTV.Barcode
FROM 
    TransVouch TV
RIGHT JOIN 
    TransVouchs TVS ON TV.ID = TVS.ID
RIGHT JOIN 
    BoxdetailTransVouch BDTV ON TVS.autoID = BDTV.RdsID;

4. 交叉连接 (CROSS JOIN)

实例：展示所有单据、物料和条码信息的任意组合（实际业务中很少使用，主要用于理论展示或特定分析）。

SELECT 
    TV.ID, TV.VouchDate, TVS.MaterID, BDTV.Barcode
FROM 
    TransVouch TV
CROSS JOIN 
    TransVouchs TVS
CROSS JOIN 
    BoxdetailTransVouch BDTV;

5. 模拟全外连接 (FULL JOIN) 使用 UNION

实例：展示所有单据及其物料信息，无论是否有对应的条码信息，同时显示所有有条码信息的物料及其单据信息，即使某些物料未被任何单据引用。

-- 获取左外连接结果
SELECT 
    TV.ID, TV.VouchDate, TVS.MaterID, BDTV.Barcode
FROM 
    TransVouch TV
LEFT JOIN 
    TransVouchs TVS ON TV.ID = TVS.ID
LEFT JOIN 
    BoxdetailTransVouch BDTV ON TVS.autoID = BDTV.RdsID
UNION
-- 获取右外连接中未被上面结果覆盖的部分
SELECT 
    TV.ID, TV.VouchDate, TVS.MaterID, BDTV.Barcode
FROM 
    TransVouch TV
RIGHT JOIN 
    TransVouchs TVS ON TV.ID = TVS.ID
RIGHT JOIN 
    BoxdetailTransVouch BDTV ON TVS.autoID = BDTV.RdsID
WHERE 
    TV.ID IS NULL OR TVS.MaterID IS NULL;

6. 自连接 (SELF JOIN)

由于您的场景没有直接涉及自连接的需求，这里提供一个通用的例子来说明概念：

例子：假设有一个员工表（Employees），自连接查询每位员工的直接上级。

SELECT 
    E1.Name AS EmployeeName, E2.Name AS ManagerName
FROM 
    Employees E1
LEFT JOIN 
    Employees E2 ON E1.ManagerID = E2.EmployeeID;

请注意，由于MySQL不直接支持FULL JOIN，我将用UNION来模拟FULL JOIN的效果。

练习试题

基于多表连接的概念，以下是一些设计用于测试多表查询能力的试题。这些题目均围绕着多表连接的原理和应用场景，结合了内连接、外连接、交叉连接等知识点。请根据您的数据库表结构和实际学习需求适当调整。

1. 基础内连接

题目: 查询所有有条码信息的单据ID、单据日期和对应物料代码。
- 提示: 使用INNER JOIN连接TransVouch、TransVouchs和BoxdetailTransVouch表。

2. 左外连接

题目: 显示所有单据及其物料信息，即使某些物料没有条码信息。
- 提示: 应用LEFT JOIN，以TransVouch为主表。

3. 右外连接

题目: 列出所有条码信息及其对应的单据ID和物料代码，即使某些条码未关联到任何单据或物料。
- 提示: 使用RIGHT JOIN，以BoxdetailTransVouch为主表。

4. 全外连接模拟

题目: 展示所有单据、物料和条码信息，无论它们之间是否存在关联。
- 提示: 通过UNION操作结合左外和右外连接来模拟FULL JOIN。

5. 交叉连接应用

题目: 列出TransVouch表和TransVouchs表中每一条记录的所有可能组合。
- 提示: 使用CROSS JOIN来生成所有记录的笛卡尔积。

6. 自连接实例

题目: 假设TransVouch表有修改历史记录，使用自连接找出每个单据的最新版本。
- 提示: 自连接TransVouch表，对比每个单据的日期字段，找出每个ID的最大日期记录。

7. 分组与多表连接

题目: 统计每个部门下的单据总数，并按部门名称升序排列。
- 假设: 存在一个Departments表，与TransVouch表通过部门ID关联。
- 提示: 使用GROUP BY结合内连接完成统计。

8. 多重连接

题目: 查找特定日期范围内，每个客户的订单数量及总金额，包括没有订单的客户。
- 假设: 有一个Customers表，与Orders表通过客户ID关联，Orders表有商品金额字段。
- 提示: 使用左外连接和SUM()函数，结合日期范围筛选。

9. 条件连接

题目: 找出所有供应商名称为"ABC Company"的订单，列出订单号、物料代码和供应商名称。
- 假设: 存在一个Suppliers表，与TransVouch表通过供应商ID关联。
- 提示: 使用内连接，并在WHERE子句中加入供应商名称的筛选条件。

10. 分区函数与连接

题目: 对过去一年内，每个月份的销售总额进行排名，显示月份、总销售额及排名。
- 假设: TransVouchs表中有销售金额字段，且有一个日期字段。
- 提示: 使用窗口函数（如RANK()或DENSE_RANK()），结合日期函数提取月份，并进行适当的分组和排序。